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