//This generates all of the rows and cells for the report public ReportGenerator(int id, DateTime startDate, DateTime endDate, IPASEntities db) { reportId = id; startDateTime = startDate; endDateTime = endDate; repository = new Repository(db); columnHeaders = new List<string>(); rowHeaders = new List<string>(); finalReportRows = new List<FinalReportRow>(); initialReportRowsList = repository.GetReportRowsForReportId(id); reportType = repository.GetReportType(reportId); measurementList = repository.GetAllAvaliableMeasurementsManageMeasurement(); reportMeasurementList = new List<BaseFieldsViewModel>(); // Build headers title = repository.GetReportTitle(reportId); GetReportColumnList(); //Build data for report type 1(PAS Report) if (reportType == 1) { //populate reportMeasurementList with only measures that are in the report foreach (var measure in measurementList) { int? measureIdInReport = null; foreach (var row in initialReportRowsList) if (row.Type == 1 && measure.MeasurementID == Convert.ToInt32(row.Row_Item)) measureIdInReport = Convert.ToInt32(row.Row_Item); if (measureIdInReport != null) reportMeasurementList.Add(measure); } //build Rows foreach (var row in initialReportRowsList) { // Make new FinalReportrow FinalReportRow reportRow = new FinalReportRow(); switch (row.Type) { case 1: //row is a measure int measureId = Convert.ToInt32(row.Row_Item); reportRow.rowName = reportMeasurementList.Find(m => m.MeasurementID == measureId).NameMeasurement; rowHeaders.Add(reportRow.rowName); //TODO optimize call so it only makes one database call per row, instead of once per column in each row foreach (var col in columnList) { // Go through each column and get Datapoint var list = repository.GetReportDatapoint(measureId, (ReportColumnsEnum)col.Type, startDate, endDate); // Add DP to RR reportRow.measurementId = measureId; if (list == null) reportRow.rowItems.Add(new RowItemWithMetadata("", "")); else foreach (var item in list) reportRow.rowItems.Add(item.cellData == null ? new RowItemWithMetadata("", "") : new RowItemWithMetadata(item.cellData, item.cellMetadata)); } break; case 2: //row is a Display Text reportRow.rowName = row.Row_Item; rowHeaders.Add(row.Row_Item); reportRow.isHeader = true; break; case 3: //row is a Category Score switch (row.Row_Item) { case "1": GetCategoryScoreData("Key Performance", startDate, endDate, ref reportRow); break; case "2": GetCategoryScoreData("Financial", startDate, endDate, ref reportRow); break; case "3": GetCategoryScoreData("HR", startDate, endDate, ref reportRow); break; default: //error break; } break; default: //error break; } // Add RR to table finalReportRows.Add(reportRow); } } }
//Method that gets the data for how calculating a Categorys total score private void GetCategoryScoreData(string categoryType, DateTime startDate, DateTime endDate, ref FinalReportRow reportRow) { reportRow.rowName = categoryType + " Score"; rowHeaders.Add(reportRow.rowName); //calculate scores decimal monthlyScore = 0; decimal monthWeight = 0; decimal yearWeight = 0; decimal prevYearWeight = 0; decimal twoYearPrevWeight = 0; decimal threeYearPrevWeight = 0; decimal currentYearScore = 0; decimal previousYearScore = 0; decimal twoYearPreviousScore = 0; decimal threeYearPreviousScore = 0; //calculate Month Score foreach (var measurement in reportMeasurementList) { if (measurement.MeasurementCategory.Equals(categoryType) && measurement.HasGoal == 1) { var measureScoreMonth = repository.GetReportDatapoint(measurement.MeasurementID, ReportColumnsEnum.MonthScore, startDate, endDate); var measureScoreYear = repository.GetReportDatapoint(measurement.MeasurementID, ReportColumnsEnum.YTDScore, startDate, endDate); var measureScorePrevYear = repository.GetReportDatapoint(measurement.MeasurementID, ReportColumnsEnum.YTDScore, new DateTime(startDate.Year - 1, startDate.Month, 1), new DateTime(endDate.Year - 1, endDate.Month, 1)); var measureScoreTwoYearPrev = repository.GetReportDatapoint(measurement.MeasurementID, ReportColumnsEnum.YTDScore, new DateTime(startDate.Year - 2, startDate.Month, 1), new DateTime(endDate.Year - 2, endDate.Month, 1)); var measureScoreThreeYearPrev = repository.GetReportDatapoint(measurement.MeasurementID, ReportColumnsEnum.YTDScore, new DateTime(startDate.Year - 3, startDate.Month, 1), new DateTime(endDate.Year - 3, endDate.Month, 1)); if (measureScoreMonth != null) { decimal internalMonthScore; if (Decimal.TryParse(measureScoreMonth.First().cellData, out internalMonthScore)) { CalculateScoreWeight(internalMonthScore, measurement.MeasurementID, startDate, endDate, ref monthlyScore, ref monthWeight); } } if (measureScoreYear != null) { decimal internalYearScore; if (Decimal.TryParse(measureScoreYear.First().cellData, out internalYearScore)) { CalculateScoreWeight(internalYearScore, measurement.MeasurementID, startDate, endDate, ref currentYearScore, ref yearWeight); } } if (measureScorePrevYear != null) { decimal internalPrevYearScore; if (Decimal.TryParse(measureScorePrevYear.First().cellData, out internalPrevYearScore)) { CalculateScoreWeight(internalPrevYearScore, measurement.MeasurementID, new DateTime(startDate.Year - 1, startDate.Month, 1), new DateTime(endDate.Year - 1, endDate.Month, 1), ref previousYearScore, ref prevYearWeight); } } if (measureScoreTwoYearPrev != null) { decimal internalTwoPrevScore; if (Decimal.TryParse(measureScoreTwoYearPrev.First().cellData, out internalTwoPrevScore)) { CalculateScoreWeight(internalTwoPrevScore, measurement.MeasurementID, new DateTime(startDate.Year - 2, startDate.Month, 1), new DateTime(endDate.Year - 2, endDate.Month, 1), ref twoYearPreviousScore, ref twoYearPrevWeight); } } if (measureScoreThreeYearPrev != null) { decimal internalThreePrevScore; if (Decimal.TryParse(measureScoreThreeYearPrev.First().cellData, out internalThreePrevScore)) { CalculateScoreWeight(internalThreePrevScore, measurement.MeasurementID, new DateTime(startDate.Year - 3, startDate.Month, 1), new DateTime(endDate.Year - 3, endDate.Month, 1), ref threeYearPreviousScore, ref threeYearPrevWeight); } } } } if (monthWeight > 0) { monthlyScore = monthlyScore / (monthWeight / 100); } if (yearWeight > 0) { currentYearScore = currentYearScore / (yearWeight / 100); } if (prevYearWeight > 0) { previousYearScore = previousYearScore / (prevYearWeight / 100); } if (twoYearPrevWeight > 0) { twoYearPreviousScore = twoYearPreviousScore / (twoYearPrevWeight / 100); } if (threeYearPrevWeight > 0) { threeYearPreviousScore = threeYearPreviousScore / (threeYearPrevWeight / 100); } //round scores to 2 decimal places monthlyScore = Math.Round(monthlyScore, 2); currentYearScore = Math.Round(currentYearScore, 2); previousYearScore = Math.Round(previousYearScore, 2); twoYearPreviousScore = Math.Round(twoYearPreviousScore, 2); threeYearPreviousScore = Math.Round(threeYearPreviousScore, 2); //populate row foreach (var col in columnList) { switch (col.Type) { //Month score case 16: if (monthWeight > 0) { reportRow.rowItems.Add(new RowItemWithMetadata(Convert.ToString(monthlyScore), "")); } else { reportRow.rowItems.Add(new RowItemWithMetadata("", "")); } break; //Year Score case 17: if (yearWeight > 0) { reportRow.rowItems.Add(new RowItemWithMetadata(Convert.ToString(currentYearScore), "")); } else { reportRow.rowItems.Add(new RowItemWithMetadata("", "")); } break; //Previous Year Score case 4: if (prevYearWeight > 0) { reportRow.rowItems.Add(new RowItemWithMetadata(Convert.ToString(previousYearScore), "")); } else { reportRow.rowItems.Add(new RowItemWithMetadata("", "")); } break; //2 Years Previous Score case 5: if (twoYearPrevWeight > 0) { reportRow.rowItems.Add(new RowItemWithMetadata(Convert.ToString(twoYearPreviousScore), "")); } else { reportRow.rowItems.Add(new RowItemWithMetadata("", "")); } break; //3 Years Previous Score case 6: if (threeYearPrevWeight > 0) { reportRow.rowItems.Add(new RowItemWithMetadata(Convert.ToString(threeYearPreviousScore), "")); } else { reportRow.rowItems.Add(new RowItemWithMetadata("", "")); } break; case 2: for (var date = startDateTime; date <= endDateTime; date = date.AddMonths(1)) reportRow.rowItems.Add(new RowItemWithMetadata(" ", "")); break; default: reportRow.rowItems.Add(new RowItemWithMetadata("", "")); break; } } }