private ExcelReportGrid ProgrammeRepetitionsGrid(List <PassModel> passes, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].ProgrammeRepetitions)
{
var key = item.Minutes.ToString(CultureInfo.InvariantCulture);
if (!data.ContainsKey(key))
{
data.Add(key, new object[passes.Count * 2]);
}
data[key][(passIndex * 2)] = item.Factor;
data[key][(passIndex * 2) + 1] = item.PeakFactor;
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Programme Repetitions", "Minutes (Non-Peak Factor / Peak Factor)", new string[] { "NPF", "PF" }));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
private ExcelReportGrid GeneralGrid(List <PassModel> passes, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].General)
{
if (!data.ContainsKey(item.Description))
{
data.Add(item.Description, new object[passes.Count]);
}
data[item.Description][passIndex] = item.Value;
if (item.RuleId == (int)RuleID.UseSponsorExclusivity ||
item.RuleId == (int)RuleID.UseCampaignMaxSpotRatings)
{
data[item.Description][passIndex] = item.Value == "0" ? "NO" : "YES";
}
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "General"));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
private ExcelReportGrid SlottingLimitsGrid(List <PassModel> passes, IEnumerable <Demographic> demographics, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].SlottingLimits)
{
var demograpgic = demographics.First(d => d.ExternalRef == item.Demographs);
var demographicName = demograpgic != null? demograpgic.Name: item.Demographs;
if (!data.ContainsKey(demographicName))
{
data.Add(demographicName, new object[passes.Count * 3]);
}
data[demographicName][(passIndex * 3)] = item.MinimumEfficiency;
data[demographicName][(passIndex * 3) + 1] = item.MaximumEfficiency;
data[demographicName][(passIndex * 3) + 2] = item.BandingTolerance;
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Slotting Limits", "Demographs (Min Efficiency / Max Rank / Banding Tolerance)", new string[] { "ME", "MR", "BT" }));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
public void HeaderRowsAreInitWhenGridIsCreated()
{
var grid = new ExcelReportGrid();
Assert.IsNotNull(grid.HeaderRows);
Assert.AreEqual(0, grid.HeaderRows.Count);
}
private ExcelReportGrid SalesAreaPassPrioritiesGrid(List <PassModel> passes, int maxColumnsCount)
{
string daysOfWeek = "Days Of The Week";
string startDate = "Start Date";
string endDate = "End Date";
var data = new Dictionary <string, object[]>();
data.Add(daysOfWeek, new object[passes.Count]);
data.Add(startDate, new object[passes.Count]);
data.Add(endDate, new object[passes.Count]);
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
data[daysOfWeek][passIndex] = GetDaysCode(passes[passIndex].PassSalesAreaPriorities.DaysOfWeek);
data[startDate][passIndex] = GetDateTimeValue(passes[passIndex].PassSalesAreaPriorities.StartDate, passes[passIndex].PassSalesAreaPriorities.StartTime);
data[endDate][passIndex] = GetDateTimeValue(passes[passIndex].PassSalesAreaPriorities.EndDate, passes[passIndex].PassSalesAreaPriorities.EndTime);
foreach (var item in passes[passIndex].PassSalesAreaPriorities.SalesAreaPriorities)
{
if (!data.ContainsKey(item.SalesArea))
{
data.Add(item.SalesArea, new object[passes.Count]);
}
data[item.SalesArea][passIndex] = item.Priority;
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Sales Area Pass Priorities"));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
private ExcelReportGrid MinRatingPointsGrid(List <PassModel> passes, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].RatingPoints)
{
var key = item.SalesAreas?.Count() > 0
? string.Join(", ", item.SalesAreas)
: "All Sales Areas";
if (!data.ContainsKey(key))
{
data.Add(key, new object[passes.Count * 3]);
}
data[key][(passIndex * 3)] = item.OffPeakValue;
data[key][(passIndex * 3) + 1] = item.PeakValue;
data[key][(passIndex * 3) + 2] = item.MidnightToDawnValue;
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Min Rating Points", "Sales Areas", new string[] { "OP", "P", "M2D" }));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
private ExcelReportGrid ScenarioDetailsGrid(RunModel run, ScenarioModel scenario, int maxColumnsCount, DateTime reportDate)
{
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
var row1 = new ExcelReportRow();
row1.Cells.Add(new ExcelReportCell()
{
Value = "Report Date", Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.HeaderStyle.Name
});
row1.Cells.Add(new ExcelReportCell()
{
Value = string.Format(CultureInfo.InvariantCulture, "{0:dd MMMM yyyy} - {0:HH:mm:ss}", reportDate), Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.HeaderStyle.Name
});
grid.HeaderRows.Add(row1);
var row2 = new ExcelReportRow();
row2.Cells.Add(new ExcelReportCell()
{
Value = "Run Name", Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.LightHeaderStyle.Name
});
row2.Cells.Add(new ExcelReportCell()
{
Value = run.Description, Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.LightHeaderStyle.Name
});
grid.BodyRows.Add(row2);
var row3 = new ExcelReportRow();
row3.Cells.Add(new ExcelReportCell()
{
Value = "Run Id", Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.LightHeaderStyle.Name
});
row3.Cells.Add(new ExcelReportCell()
{
Value = run.Id, Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.LightHeaderStyle.Name
});
grid.BodyRows.Add(row3);
var row4 = new ExcelReportRow();
row4.Cells.Add(new ExcelReportCell()
{
Value = "Scenario Name", Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.LightHeaderStyle.Name
});
row4.Cells.Add(new ExcelReportCell()
{
Value = scenario.Name, Alignment = ExcelHorizontalAlignment.Left, StyleName = GamePlanReportStyles.LightHeaderStyle.Name
});
grid.BodyRows.Add(row4);
return(grid);
}
private void WriteGrid(ISheetBuilder sb, ExcelReportGrid grid)
{
grid.HeaderRows.ForEach(row => WriteRow(sb, grid, row));
if (grid.HasData)
{
grid.BodyRows.ForEach(row => WriteRow(sb, grid, row));
}
else
{
WriteNoDataRow(sb, grid.MaxColumnCount);
}
}
public void HasDataReturnFalseWhenNoneOfTheCellsOfTheGridHasAnyValue()
{
var grid = new ExcelReportGrid();
var row1 = new ExcelReportRow();
var row2 = new ExcelReportRow();
row1.Cells.Add(new ExcelReportCell());
row2.Cells.Add(new ExcelReportCell());
grid.BodyRows.Add(row1);
grid.BodyRows.Add(row2);
Assert.IsFalse(grid.HasData);
}
private void AssertGridData(string[] expectedData, ExcelReportGrid grid)
{
var index = 0;
grid.HeaderRows.ForEach(r =>
{
Assert.AreEqual(expectedData[index++], GetRowValue(r));
});
grid.BodyRows.ForEach(r =>
{
Assert.AreEqual(expectedData[index++], GetRowValue(r));
});
}
public void HasDataReturnFalseWhenOnlyFirstColumnHasValue()
{
var grid = new ExcelReportGrid();
var row1 = new ExcelReportRow();
var row2 = new ExcelReportRow();
row1.Cells.Add(new ExcelReportCell()
{
Value = 10
});
row2.Cells.Add(new ExcelReportCell());
grid.BodyRows.Add(row1);
grid.BodyRows.Add(row2);
Assert.IsFalse(grid.HasData);
}
public void HasDataReturnTrueWhenAtleastOneOfTheCellsNotInTheFirstColumnHasValue()
{
var grid = new ExcelReportGrid();
var row1 = new ExcelReportRow();
var row2 = new ExcelReportRow();
row1.Cells.Add(new ExcelReportCell());
row1.Cells.Add(new ExcelReportCell()
{
Value = 10
});
row2.Cells.Add(new ExcelReportCell());
grid.BodyRows.Add(row1);
grid.BodyRows.Add(row2);
Assert.IsTrue(grid.HasData);
}
private void WriteRow(ISheetBuilder sb, ExcelReportGrid grid, ExcelReportRow row)
{
sb.Block(bb =>
{
for (var i = 0; i < row.Cells.Count; i++)
{
var cell = row.Cells[i];
var colSpan = 1;
if (i > 0)
{
colSpan = (grid.MaxColumnCount - 1) / (row.Cells.Count - 1);
}
writeCell(bb, cell, colSpan);
}
});
}
private void AssertGridStyle(ExcelReportGrid grid, ExcelHorizontalAlignment secondColumnAlignmnet, string firstColumnStyleName, string otherColumnStyleName, bool alternateColor)
{
var rowIndex = 0;
grid.HeaderRows.ForEach(r =>
{
var cellIndex = 0;
r.Cells.ForEach(cell => AssertHeaderCellStyle(cell, secondColumnAlignmnet, rowIndex, cellIndex++));
rowIndex++;
});
rowIndex = 0;
grid.BodyRows.ForEach(r =>
{
var cellIndex = 0;
r.Cells.ForEach(cell => AssertBodyCellStyle(cell, firstColumnStyleName, otherColumnStyleName, secondColumnAlignmnet, rowIndex, cellIndex++, alternateColor));
rowIndex++;
});
}
private ExcelReportGrid ToleranceGrid(List <PassModel> passes, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].Tolerances)
{
if (!data.ContainsKey(item.Description))
{
data.Add(item.Description, new object[passes.Count * 4]);
}
data[item.Description][passIndex * 4] = item.Under;
data[item.Description][(passIndex * 4) + 1] = item.Over;
switch (item.ForceUnderOver)
{
case ForceOverUnder.Over:
data[item.Description][(passIndex * 4) + 2] = "Over";
break;
case ForceOverUnder.Under:
data[item.Description][(passIndex * 4) + 2] = "Under";
break;
default:
data[item.Description][(passIndex * 4) + 2] = "None";
break;
}
data[item.Description][(passIndex * 4) + 3] = item.Ignore ? "YES" : "";
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Tolerance", "Under/Over/Force/Ignore", new[] { "U", "O", "F", "I" }));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
private ExcelReportGrid WeightingGrid(List <PassModel> passes, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].Weightings)
{
if (!data.ContainsKey(item.Description))
{
data.Add(item.Description, new object[passes.Count]);
}
data[item.Description][passIndex] = item.Value;
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Weightings"));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
private ExcelReportGrid RulesGrid(List <PassModel> passes, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].Rules)
{
if (!data.ContainsKey(item.Description))
{
data.Add(item.Description, new object[passes.Count * 2]);
}
data[item.Description][passIndex * 2] = item.Value;
data[item.Description][(passIndex * 2) + 1] = item.PeakValue;
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Rules", "Daypart (Non-Peak / Peak)", new[] { "NP", "P" }));
grid.BodyRows.AddRange(GetBodyRows(data));
return(grid);
}
private ExcelReportGrid BreakExclusionsGrid(List <PassModel> passes, int maxColumnsCount)
{
var data = new Dictionary <string, object[]>();
for (int passIndex = 0; passIndex < passes.Count; passIndex++)
{
foreach (var item in passes[passIndex].BreakExclusions)
{
string breakExclusionsCode = GetBreakExclusionsCode(item);
if (!data.ContainsKey(breakExclusionsCode))
{
data.Add(breakExclusionsCode, new object[passes.Count]);
}
data[breakExclusionsCode][passIndex] = "YES";
}
}
var grid = new ExcelReportGrid();
grid.MaxColumnCount = maxColumnsCount;
grid.HeaderRows.AddRange(GetGenericHeaderRowsForRunReport(passes, "Break Exclusions"));
grid.BodyRows.AddRange(GetBodyRows(data, GamePlanReportStyles.CourierNewFontCellStyle.Name));
return(grid);
}