protected override void RenderCommonData(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory, GroupStats statsObj)
{
int colIdx_1stCommonColumn = startColIdx;
int renderColIdx = colIdx_1stCommonColumn;
int renderRowIdx = startRowIdx;
ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalOccupancyTime));
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.MaximumPotentialOccupancyTime));
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.PercentageOccupancy);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.ingress);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.egress);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 2, renderRowIdx, renderRowIdx, "###0.00", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 3, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 4, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
// And now lets autosize the columns
for (int autoSizeColIdx = colIdx_1stCommonColumn; autoSizeColIdx <= renderColIdx; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, renderRowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
col.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
// And now finally we must manually size the columns that have wrap text (autofit doesn't work nicely when we have wrap text)
ws.Column(colIdx_1stCommonColumn + 1).Width = 20;
ws.Column(colIdx_1stCommonColumn + 3).Width = 12;
ws.Column(colIdx_1stCommonColumn + 4).Width = 12;
if (this._ReportParams.IncludeHourlyStatistics == true)
{
// Now we will construct the hourly category column, followed by hour detail columns
ws.SetValue(renderRowIdx + 0, colIdx_HourlyCategory, "Occupied Duration");
ws.SetValue(renderRowIdx + 1, colIdx_HourlyCategory, "Max Possible Duration");
ws.SetValue(renderRowIdx + 2, colIdx_HourlyCategory, "% Occupied");
ws.SetValue(renderRowIdx + 3, colIdx_HourlyCategory, "Arrivals");
ws.SetValue(renderRowIdx + 4, colIdx_HourlyCategory, "Departures");
using (OfficeOpenXml.ExcelRange col = ws.Cells[renderRowIdx, colIdx_HourlyCategory, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory])
{
col.Style.Font.Bold = true;
}
MergeCellRange(ws, renderRowIdx + 1, 1, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory - 1);
}
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
{
timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);// DateTime.Now;//Datetime.Now;//DateTime.Now;//Datetime.Now;
// Now gather and analyze data for the report
SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
requiredDataElements.NeedsSensorData = true;
requiredDataElements.NeedsPaymentData = false;
requiredDataElements.NeedsOverstayData = false;
requiredDataElements.NeedsEnforcementActionData = false;
this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, _ReportParams);
this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result);
OfficeOpenXml.ExcelWorksheet ws = null;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
// Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Summary");
// Render the standard report title lines
rowIdx = 1; // Excel uses 1-based indexes
colIdx = 1;
RenderCommonReportTitle(ws, this._ReportName);
// Render common report header for enforcement activity restriction filter, but only if its not for all activity
if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity)
{
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_ActionTakenRestrictions(ws);
}
// Render common report header for regulated hour restriction filter
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
}
rowIdx++;
colIdx = 1;
int hyperlinkstartRowIdx = rowIdx;
if (_ReportParams.IncludeMeterSummary == true)
{
RenderWorksheetHyperlink(ws, "Meter Occupancy", "Meter Occupancy summary");
}
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderWorksheetHyperlink(ws, "Space Occupancy", "Space Occupancy summary");
}
if (_ReportParams.IncludeAreaSummary == true)
{
RenderWorksheetHyperlink(ws, "Area Occupancy", "Area Occupancy summary");
}
if (_ReportParams.IncludeDailySummary == true)
{
RenderWorksheetHyperlink(ws, "Daily Occupancy", "Daily Occupancy summary");
}
if (_ReportParams.IncludeMonthlySummary == true)
{
RenderWorksheetHyperlink(ws, "Monthly Occupancy", "Monthly Occupancy summary");
}
if (_ReportParams.IncludeDetailRecords == true)
{
RenderWorksheetHyperlink(ws, "Details", "Occupancy details");
}
rowIdx++;
rowIdx++;
colIdx = 1;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
}
// Now start the report data summary header
RenderOverallReportSummary(ws);
// --- END OF OVERALL SUMMARY WORKSHEET ---
// Should we include a worksheet with Meter aggregates?
if (_ReportParams.IncludeMeterSummary == true)
{
RenderMeterSummaryWorksheet(pck, "Meter Occupancy");
}
// Should we include a worksheet with Space aggregates?
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderSpaceSummaryWorksheet(pck, "Space Occupancy");
}
// Should we include a worksheet with Area aggregates?
if (_ReportParams.IncludeAreaSummary == true)
{
RenderAreaSummaryWorksheet(pck, "Area Occupancy");
}
// Should we include a worksheet with Daily aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderDailySummaryWorksheet(pck, "Daily Occupancy");
}
// Should we include a worksheet with Monthly aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderMonthlySummaryWorksheet(pck, "Monthly Occupancy");
}
// Should we include a Details worksheet?
if (_ReportParams.IncludeDetailRecords == true)
{
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Details");
int detailColumnCount = 8;
// Render the header row
rowIdx = 1; // Excel uses 1-based indexes
ws.SetValue(rowIdx, 1, "Space #");
ws.SetValue(rowIdx, 2, "Meter #");
ws.SetValue(rowIdx, 3, "Area #");
ws.SetValue(rowIdx, 4, "Area");
ws.SetValue(rowIdx, 5, "Event Start");
ws.SetValue(rowIdx, 6, "Event End");
ws.SetValue(rowIdx, 7, "Occupied?");
ws.SetValue(rowIdx, 8, "Duration");
// Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, detailColumnCount])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
// Increment the row index, which will now be the 1st row of our data
rowIdx++;
#region Populate data for each record
foreach (SpaceAsset spaceAsset in this._ReportEngine.ReportDataModel.SpacesIncludedInReport)
{
List <SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent> spaceRecs = this._ReportEngine.ReportDataModel.FindRecsForBayAndMeter(spaceAsset.SpaceID, spaceAsset.MeterID);
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent nextEvent in spaceRecs)
{
// Don't detail this item if its a "dummy" event
if (nextEvent.IsDummySensorEvent == true)
{
continue;
}
AreaAsset areaAsset = _ReportEngine.GetAreaAsset(spaceAsset.AreaID_PreferLibertyBeforeInternal);
// Output row values for data
ws.SetValue(rowIdx, 1, spaceAsset.SpaceID);
ws.SetValue(rowIdx, 2, spaceAsset.MeterID);
if (areaAsset != null)
{
ws.SetValue(rowIdx, 3, areaAsset.AreaID);
ws.SetValue(rowIdx, 4, areaAsset.AreaName);
}
ws.SetValue(rowIdx, 5, nextEvent.SensorEvent_Start);
ws.SetValue(rowIdx, 6, nextEvent.SensorEvent_End);
if (nextEvent.SensorEvent_IsOccupied == true)
{
ws.SetValue(rowIdx, 7, "Y");
}
else
{
ws.SetValue(rowIdx, 7, "N");
}
ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(nextEvent.SensorEvent_Duration));
// Increment the row index, which will now be the next row of our data
rowIdx++;
// Is there a child "repeat" event also?
if (nextEvent.RepeatSensorEvents != null)
{
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repeatEvent in nextEvent.RepeatSensorEvents)
{
ws.SetValue(rowIdx, 1, spaceAsset.SpaceID);
ws.SetValue(rowIdx, 2, spaceAsset.MeterID);
if (areaAsset != null)
{
ws.SetValue(rowIdx, 3, areaAsset.AreaID);
ws.SetValue(rowIdx, 4, areaAsset.AreaName);
}
ws.SetValue(rowIdx, 5, repeatEvent.SensorEvent_Start);
ws.SetValue(rowIdx, 6, repeatEvent.SensorEvent_End);
if (repeatEvent.SensorEvent_IsOccupied == true)
{
ws.SetValue(rowIdx, 7, "Y");
}
else
{
ws.SetValue(rowIdx, 7, "N");
}
ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(repeatEvent.SensorEvent_Duration));
// Increment the row index, which will now be the next row of our data
rowIdx++;
}
}
}
}
#endregion
// We will add autofilters to our headers so user can sort the columns easier
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, rowIdx, detailColumnCount])
{
rng.AutoFilter = true;
}
// Apply formatting to the columns as appropriate (Starting row is 2 (first row of data), and ending row is the current rowIdx value)
ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 2, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 3, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 5, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 6, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 7, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned
ApplyNumberStyleToColumn(ws, 8, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned
// And now lets size the columns
for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
}
}
}
// All cells in spreadsheet are populated now, so render (save the file) to a memory stream
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
// Stop diagnostics timer
sw.Stop();
System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString());
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
{
timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
// Now gather and analyze data for the report
SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
requiredDataElements.NeedsSensorData = true;
requiredDataElements.NeedsPaymentData = false;
requiredDataElements.NeedsOverstayData = true;
requiredDataElements.NeedsEnforcementActionData = true;
this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, this._ReportParams);
this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result);
OfficeOpenXml.ExcelWorksheet ws = null;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
// Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
ws = pck.Workbook.Worksheets.Add("Details");
// Render the standard report title lines
rowIdx = 1; // Excel uses 1-based indexes
colIdx = 1;
RenderCommonReportTitle(ws, this._ReportName);
// Render common report header for enforcement activity restriction filter, but only if its not for all activity
if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity)
{
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_ActionTakenRestrictions(ws);
}
// Render common report header for regulated hour restriction filter
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
}
rowIdx++;
rowIdx++;
colIdx = 1;
int detailsStartRow = rowIdx;
int detailColumnCount = 29;
// Render the header row
ws.SetValue(rowIdx, 1, "Space #");
ws.SetValue(rowIdx, 2, "Meter #");
ws.SetValue(rowIdx, 3, "Area #");
ws.SetValue(rowIdx, 4, "Area");
ws.SetValue(rowIdx, 5, "Event Timestamp");
ws.SetValue(rowIdx, 6, "Record Timestamp");
ws.SetValue(rowIdx, 7, "Latency");
ws.SetValue(rowIdx, 8, "Occupied");
ws.SetValue(rowIdx, 9, "Vacant Duration");
ws.SetValue(rowIdx, 10, "Time Arrived");
ws.SetValue(rowIdx, 11, "Time Departed");
ws.SetValue(rowIdx, 12, "Parked Duration");
ws.SetValue(rowIdx, 13, "Max Stay Regulation");
ws.SetValue(rowIdx, 14, "Overstay Violation");
ws.SetValue(rowIdx, 15, "Overstay Duration");
ws.SetValue(rowIdx, 16, "Overstay (0-15min)");
ws.SetValue(rowIdx, 17, "Overstay (15-30min)");
ws.SetValue(rowIdx, 18, "Overstay (30-60min)");
ws.SetValue(rowIdx, 19, "Overstay (>60min)");
ws.SetValue(rowIdx, 20, "Violation Actioned");
ws.SetValue(rowIdx, 21, "Violation Issued");
ws.SetValue(rowIdx, 22, "Violation Warning");
ws.SetValue(rowIdx, 23, "Violation Not Issued");
ws.SetValue(rowIdx, 24, "Violation Fault");
ws.SetValue(rowIdx, 25, "Violation Missed");
ws.SetValue(rowIdx, 26, "Capture Rate (0-15min)");
ws.SetValue(rowIdx, 27, "Capture Rate (15-30min)");
ws.SetValue(rowIdx, 28, "Capture Rate (30-60min)");
ws.SetValue(rowIdx, 29, "Capture Rate (>60min)");
// Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 1, detailsStartRow, 4])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 5, detailsStartRow, 8])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(36, 64, 98));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 9, detailsStartRow, 13])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(0, 176, 80));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 14, detailsStartRow, 29])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(150, 54, 52));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
// Increment the row index, which will now be the 1st row of our data
rowIdx++;
detailsStartRow = rowIdx;
timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
GroupStats baseTotalStats = this._ReportEngine.GetOverallStats(timeIsolation);
ParkingAndOverstayGroupStats totalStats = new ParkingAndOverstayGroupStats(baseTotalStats);
#region Populate data for each record
foreach (AreaAsset areaAsset in this._ReportEngine.ReportDataModel.AreasIncludedInReport)
{
GroupStats baseAreaStats = this._ReportEngine.GetAreaStats(areaAsset.AreaID, timeIsolation);
ParkingAndOverstayGroupStats areaStats = new ParkingAndOverstayGroupStats(baseAreaStats);
foreach (SpaceAsset spaceAsset in this._ReportEngine.ReportDataModel.SpacesIncludedInReport)
{
// Skip record if its not applicable to the current area we are processing
if (spaceAsset.AreaID_PreferLibertyBeforeInternal != areaAsset.AreaID)
{
continue;
}
List <SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent> spaceRecs = this._ReportEngine.ReportDataModel.FindRecsForBayAndMeter(spaceAsset.SpaceID, spaceAsset.MeterID);
TimeSpan previousVacantDuration = new TimeSpan(0);
SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent previousOccupiedEvent = null;
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repEvents in spaceRecs)
{
ws.SetValue(rowIdx, 1, repEvents.BayInfo.SpaceID);
ws.SetValue(rowIdx, 2, repEvents.BayInfo.MeterID);
if (areaAsset != null)
{
ws.SetValue(rowIdx, 3, areaAsset.AreaID);
ws.SetValue(rowIdx, 4, areaAsset.AreaName);
}
ws.SetValue(rowIdx, 5, repEvents.SensorEvent_Start);
ws.SetValue(rowIdx, 6, repEvents.SensorEvent_RecCreationDateTime);
ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(repEvents.SensorEvent_Latency));
if (repEvents.SensorEvent_IsOccupied == true)
{
previousOccupiedEvent = repEvents;
ws.SetValue(rowIdx, 8, 1); // 1 for numeric version of "True"
if (previousVacantDuration.Ticks > 0)
{
ws.SetValue(rowIdx, 9, FormatTimeSpanAsHoursMinutesAndSeconds(previousVacantDuration));
}
ws.SetValue(rowIdx, 10, repEvents.SensorEvent_Start);
}
else
{
ws.SetValue(rowIdx, 8, 0); // 1 for numeric version of "False"
ws.SetValue(rowIdx, 11, repEvents.SensorEvent_Start);
if (previousOccupiedEvent != null)
{
bool firstOverstay = true;
if (previousOccupiedEvent.Overstays.Count == 0)
{
ws.SetValue(rowIdx, 11, previousOccupiedEvent.SensorEvent_Start);
ws.SetValue(rowIdx, 12, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Duration));
RegulatedHoursDetail ruleForEvent = GetRegulationRuleAtEventTime(repEvents);
if (ruleForEvent != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(ruleForEvent.MaxStayMinutes.ToString());
ws.SetValue(rowIdx, 13, sb.ToString());
}
ws.SetValue(rowIdx, 14, "N");
}
foreach (SensorAndPaymentReportEngine.OverstayVioEvent overstay in previousOccupiedEvent.Overstays)
{
if (firstOverstay == false)
{
// Need to start new row and repeat the header info!
rowIdx++;
ws.SetValue(rowIdx, 1, previousOccupiedEvent.BayInfo.SpaceID);
ws.SetValue(rowIdx, 2, previousOccupiedEvent.BayInfo.MeterID);
if (areaAsset != null)
{
ws.SetValue(rowIdx, 3, areaAsset.AreaID);
ws.SetValue(rowIdx, 4, areaAsset.AreaName);
}
ws.SetValue(rowIdx, 5, previousOccupiedEvent.SensorEvent_Start);
ws.SetValue(rowIdx, 6, previousOccupiedEvent.SensorEvent_RecCreationDateTime);
ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Latency));
ws.SetValue(rowIdx, 8, 0); // 1 for numeric version of "False"
ws.SetValue(rowIdx, 11, previousOccupiedEvent.SensorEvent_Start);
}
ws.SetValue(rowIdx, 12, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Duration));
if (overstay.OverstayBasedOnRuleDetail != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(overstay.OverstayBasedOnRuleDetail.MaxStayMinutes.ToString());
ws.SetValue(rowIdx, 13, sb.ToString());
}
ws.SetValue(rowIdx, 14, "Y");
ws.SetValue(rowIdx, 15, FormatTimeSpanAsHoursMinutesAndSeconds(overstay.DurationOfTimeBeyondStayLimits));
if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 15)
{
ws.SetValue(rowIdx, 16, "Y");
areaStats.TotalOverstaysDuration0To15Mins++;
totalStats.TotalOverstaysDuration0To15Mins++;
}
else if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 30)
{
ws.SetValue(rowIdx, 17, "Y");
areaStats.TotalOverstaysDuration15To30Mins++;
totalStats.TotalOverstaysDuration15To30Mins++;
}
else if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 60)
{
ws.SetValue(rowIdx, 18, "Y");
areaStats.TotalOverstaysDuration30To60Mins++;
totalStats.TotalOverstaysDuration30To60Mins++;
}
else
{
ws.SetValue(rowIdx, 19, "Y");
areaStats.TotalOverstaysDurationOver60Mins++;
totalStats.TotalOverstaysDurationOver60Mins++;
}
if (!string.IsNullOrEmpty(overstay.EnforcementActionTaken))
{
ws.SetValue(rowIdx, 20, "Y");
}
else
{
ws.SetValue(rowIdx, 20, "N");
}
if (string.Compare(overstay.EnforcementActionTaken, "Enforced", true) == 0)
{
ws.SetValue(rowIdx, 21, "Y");
}
else
{
ws.SetValue(rowIdx, 21, "N");
}
if (string.Compare(overstay.EnforcementActionTaken, "Cautioned", true) == 0)
{
ws.SetValue(rowIdx, 22, "Y");
}
else
{
ws.SetValue(rowIdx, 22, "N");
}
if (string.Compare(overstay.EnforcementActionTaken, "NotEnforced", true) == 0)
{
ws.SetValue(rowIdx, 23, "Y");
}
else
{
ws.SetValue(rowIdx, 23, "N");
}
if (string.Compare(overstay.EnforcementActionTaken, "Fault", true) == 0)
{
ws.SetValue(rowIdx, 24, "Y");
}
else
{
ws.SetValue(rowIdx, 24, "N");
}
if (string.IsNullOrEmpty(overstay.EnforcementActionTaken))
{
ws.SetValue(rowIdx, 25, "Y");
ws.SetValue(rowIdx, 26, "N");
ws.SetValue(rowIdx, 27, "N");
ws.SetValue(rowIdx, 28, "N");
ws.SetValue(rowIdx, 29, "N");
}
else
{
ws.SetValue(rowIdx, 25, "N");
ws.SetValue(rowIdx, 26, "N");
ws.SetValue(rowIdx, 27, "N");
ws.SetValue(rowIdx, 28, "N");
ws.SetValue(rowIdx, 29, "N");
TimeSpan captureRate = (overstay.EnforcementActionTakenTimeStamp - overstay.StartOfOverstayViolation);
if (captureRate.TotalMinutes < 15)
{
ws.SetValue(rowIdx, 26, "Y");
areaStats.TotalOverstaysActioned0To15Mins++;
totalStats.TotalOverstaysActioned0To15Mins++;
}
else if (captureRate.TotalMinutes < 30)
{
ws.SetValue(rowIdx, 27, "Y");
areaStats.TotalOverstaysActioned15To30Mins++;
totalStats.TotalOverstaysActioned15To30Mins++;
}
else if (captureRate.TotalMinutes < 60)
{
ws.SetValue(rowIdx, 28, "Y");
areaStats.TotalOverstaysActioned30To60Mins++;
totalStats.TotalOverstaysActioned30To60Mins++;
}
else
{
ws.SetValue(rowIdx, 29, "Y");
areaStats.TotalOverstaysActionedOver60Mins++;
totalStats.TotalOverstaysActionedOver60Mins++;
}
}
// Set flag so we know we're no longer dealing with the first overstay of this occupied event
firstOverstay = false;
}
}
}
if (repEvents.SensorEvent_IsOccupied == false)
{
previousVacantDuration = new TimeSpan(repEvents.SensorEvent_Duration.Ticks);
}
// Increment the row index, which will now be the next row of our data
rowIdx++;
}
}
// Finish the area aggregations
areaStats.AggregateSelf();
colIdx = 1;
ws.SetValue(rowIdx, colIdx, "SUBTOTAL AREA");
MergeCellRange(ws, rowIdx, colIdx, rowIdx, 4);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, colIdx, rowIdx, 29])
{
rng.Style.Font.Bold = true;
}
ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(areaStats.AverageLatency));
ws.SetValue(rowIdx, 8, areaStats.ingress);
ws.SetValue(rowIdx, 9, areaStats.PercentVacantDuration.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 12, areaStats.PercentageOccupancy.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 14, areaStats.PercentOverstayedCount.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 15, areaStats.PercentageOverstayedDuration.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 16, areaStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 17, areaStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 18, areaStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 19, areaStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 20, areaStats.PercentOverstaysActioned.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 21, areaStats.PercentOverstaysIssued.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 22, areaStats.PercentOverstaysCautioned.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 23, areaStats.PercentOverstaysNotIssued.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 24, areaStats.PercentOverstaysFault.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 25, areaStats.PercentOverstaysMissed.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 26, areaStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 27, areaStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 28, areaStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 29, areaStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");
rowIdx++;
rowIdx++;
}
// Finish the total aggregations
totalStats.AggregateSelf();
colIdx = 1;
ws.SetValue(rowIdx, colIdx, "TOTAL");
MergeCellRange(ws, rowIdx, colIdx, rowIdx, 4);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, colIdx, rowIdx, 29])
{
rng.Style.Font.Bold = true;
}
ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(totalStats.AverageLatency));
ws.SetValue(rowIdx, 8, totalStats.ingress);
ws.SetValue(rowIdx, 9, totalStats.PercentVacantDuration.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 12, totalStats.PercentageOccupancy.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 14, totalStats.PercentOverstayedCount.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 15, totalStats.PercentageOverstayedDuration.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 16, totalStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 17, totalStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 18, totalStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 19, totalStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 20, totalStats.PercentOverstaysActioned.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 21, totalStats.PercentOverstaysIssued.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 22, totalStats.PercentOverstaysCautioned.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 23, totalStats.PercentOverstaysNotIssued.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 24, totalStats.PercentOverstaysFault.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 25, totalStats.PercentOverstaysMissed.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 26, totalStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 27, totalStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 28, totalStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%");
ws.SetValue(rowIdx, 29, totalStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%");
rowIdx++;
rowIdx++;
#endregion
// AutoFilters aren't suitable for this report
/*
* // We will add autofilters to our headers so user can sort the columns easier
* using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow - 1, 1, rowIdx, detailColumnCount])
* {
* rng.AutoFilter = true;
* }
*/
ApplyNumberStyleToColumn(ws, 1, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 2, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 3, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 4, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 5, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 6, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 7, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 8, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 9, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 10, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 11, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 12, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 13, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 14, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 15, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 16, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 17, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 18, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 19, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 20, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 21, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 22, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 23, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 24, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 25, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 26, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 27, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 28, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 29, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right);
// And now lets size the columns
for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[detailsStartRow - 1, autoSizeColIdx, rowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
}
}
// And finally we will freeze the header rows for nicer scrolling
ws.View.FreezePanes(7, 1);
// All cells in spreadsheet are populated now, so render (save the file) to a memory stream
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
// Stop diagnostics timer
sw.Stop();
System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString());
}
protected override void RenderCommonData(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory, GroupStats statsObj)
{
int colIdx_1stCommonColumn = startColIdx;
int renderColIdx = colIdx_1stCommonColumn;
int renderRowIdx = startRowIdx;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.OverstayCount);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.PayVioCount);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.OverstayCount + statsObj.PayVioCount);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysActioned + statsObj.TotalPayViosActioned);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysEnforced + statsObj.TotalPayViosEnforced);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysCautioned + statsObj.TotalPayViosCautioned);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysNotEnforced + statsObj.TotalPayViosNotEnforced);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysFaulty + statsObj.TotalPayViosFaulty);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalViosActioned0To15Mins);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalViosActioned15To40Mins);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalViosActionedOver40Mins);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, (statsObj.OverstayCount + statsObj.PayVioCount) - (statsObj.TotalOverstaysActioned + statsObj.TotalPayViosActioned));
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 0, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 1, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 2, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 3, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 4, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 5, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 6, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 7, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 8, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 9, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 10, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 11, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
// And now lets autosize the columns
for (int autoSizeColIdx = colIdx_1stCommonColumn; autoSizeColIdx <= renderColIdx; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, renderRowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
col.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
// And now finally we must manually size the columns that have wrap text (autofit doesn't work nicely when we have wrap text)
ws.Column(colIdx_1stCommonColumn + 0).Width = 20;
ws.Column(colIdx_1stCommonColumn + 1).Width = 20;
ws.Column(colIdx_1stCommonColumn + 2).Width = 20;
ws.Column(colIdx_1stCommonColumn + 3).Width = 20;
ws.Column(colIdx_1stCommonColumn + 4).Width = 20;
ws.Column(colIdx_1stCommonColumn + 5).Width = 20;
ws.Column(colIdx_1stCommonColumn + 6).Width = 20;
ws.Column(colIdx_1stCommonColumn + 8).Width = 20;
ws.Column(colIdx_1stCommonColumn + 9).Width = 20;
ws.Column(colIdx_1stCommonColumn + 10).Width = 20;
ws.Column(colIdx_1stCommonColumn + 11).Width = 20;
if (this._ReportParams.IncludeHourlyStatistics == true)
{
// Now we will construct the hourly category column, followed by hour detail columns
ws.SetValue(renderRowIdx + 0, colIdx_HourlyCategory, "Overstay Violations");
ws.SetValue(renderRowIdx + 1, colIdx_HourlyCategory, "Payment Violations");
ws.SetValue(renderRowIdx + 2, colIdx_HourlyCategory, "Total Violations");
ws.SetValue(renderRowIdx + 3, colIdx_HourlyCategory, "Total Actioned");
ws.SetValue(renderRowIdx + 4, colIdx_HourlyCategory, "Total Enforced");
ws.SetValue(renderRowIdx + 5, colIdx_HourlyCategory, "Total Cautioned");
ws.SetValue(renderRowIdx + 6, colIdx_HourlyCategory, "Total Not Enforced");
ws.SetValue(renderRowIdx + 7, colIdx_HourlyCategory, "Total Faulty");
ws.SetValue(renderRowIdx + 8, colIdx_HourlyCategory, "Actioned < 15 mins");
ws.SetValue(renderRowIdx + 9, colIdx_HourlyCategory, "Actioned 15 - 40 mins");
ws.SetValue(renderRowIdx + 10, colIdx_HourlyCategory, "Actioned > 40 mins");
ws.SetValue(renderRowIdx + 11, colIdx_HourlyCategory, "Total Missed");
using (OfficeOpenXml.ExcelRange col = ws.Cells[renderRowIdx, colIdx_HourlyCategory, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory])
{
col.Style.Font.Bold = true;
}
MergeCellRange(ws, renderRowIdx + 1, 1, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory - 1);
}
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
{
timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
// Now gather and analyze data for the report
SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
requiredDataElements.NeedsSensorData = true;
requiredDataElements.NeedsPaymentData = true;
requiredDataElements.NeedsOverstayData = true;
requiredDataElements.NeedsEnforcementActionData = true;
this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, this._ReportParams);
this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result);
OfficeOpenXml.ExcelWorksheet ws = null;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
// Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
ws = pck.Workbook.Worksheets.Add("Summary");
// Render the standard report title lines
rowIdx = 1; // Excel uses 1-based indexes
colIdx = 1;
RenderCommonReportTitle(ws, this._ReportName);
// Render common report header for enforcement activity restriction filter, but only if its not for all activity
if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity)
{
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_ActionTakenRestrictions(ws);
}
// Render common report header for regulated hour restriction filter
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
}
rowIdx++;
colIdx = 1;
int hyperlinkstartRowIdx = rowIdx;
if (_ReportParams.IncludeMeterSummary == true)
{
RenderWorksheetHyperlink(ws, "Meter Enforcement", "Meter Enforcement summary");
}
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderWorksheetHyperlink(ws, "Space Enforcement", "Space Enforcement summary");
}
if (_ReportParams.IncludeAreaSummary == true)
{
RenderWorksheetHyperlink(ws, "Area Enforcement", "Area Enforcement summary");
}
if (_ReportParams.IncludeDailySummary == true)
{
RenderWorksheetHyperlink(ws, "Daily Enforcement", "Daily Enforcement summary");
}
if (_ReportParams.IncludeMonthlySummary == true)
{
RenderWorksheetHyperlink(ws, "Monthly Enforcement", "Monthly Enforcement summary");
}
if (_ReportParams.IncludeDetailRecords == true)
{
RenderWorksheetHyperlink(ws, "Details", "Enforcement details");
}
rowIdx++;
rowIdx++;
colIdx = 1;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
}
// Now start the report data summary header
RenderOverallReportSummary(ws);
// --- END OF OVERALL SUMMARY WORKSHEET ---
// Should we include a worksheet with Meter aggregates?
if (_ReportParams.IncludeMeterSummary == true)
{
RenderMeterSummaryWorksheet(pck, "Meter Enforcement");
}
// Should we include a worksheet with Space aggregates?
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderSpaceSummaryWorksheet(pck, "Space Enforcement");
}
// Should we include a worksheet with Area aggregates?
if (_ReportParams.IncludeAreaSummary == true)
{
RenderAreaSummaryWorksheet(pck, "Area Enforcement");
}
// Should we include a worksheet with Daily aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderDailySummaryWorksheet(pck, "Daily Enforcement");
}
// Should we include a worksheet with Monthly aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderMonthlySummaryWorksheet(pck, "Monthly Enforcement");
}
// Should we include a Details worksheet?
if (_ReportParams.IncludeDetailRecords == true)
{
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Details");
int detailColumnCount = 18;
// Render the header row
rowIdx = 1; // Excel uses 1-based indexes
ws.SetValue(rowIdx, 1, "Space #");
ws.SetValue(rowIdx, 2, "Meter #");
ws.SetValue(rowIdx, 3, "Area #");
ws.SetValue(rowIdx, 4, "Area");
ws.SetValue(rowIdx, 5, "Arrival");
ws.SetValue(rowIdx, 6, "Departure");
ws.SetValue(rowIdx, 7, "Start of" + Environment.NewLine + "Overstay Violation");
ApplyWrapTextStyleToCell(ws, rowIdx, 7);
ws.SetValue(rowIdx, 8, "Overstay Violation" + Environment.NewLine + "Duration");
ApplyWrapTextStyleToCell(ws, rowIdx, 8);
ws.SetValue(rowIdx, 9, "Overstay Violation" + Environment.NewLine + "Action Taken");
ApplyWrapTextStyleToCell(ws, rowIdx, 9);
ws.SetValue(rowIdx, 10, "Overstay Violation" + Environment.NewLine + "Action Taken Timestamp");
ApplyWrapTextStyleToCell(ws, rowIdx, 10);
ws.SetValue(rowIdx, 11, "Overstay Rule");
ws.SetValue(rowIdx, 12, "Payment Timestamp");
ws.SetValue(rowIdx, 13, "Payment Expiration");
ws.SetValue(rowIdx, 14, "Payment Zeroed-out" + Environment.NewLine + "Timestamp");
ApplyWrapTextStyleToCell(ws, rowIdx, 14);
ws.SetValue(rowIdx, 15, "Start of" + Environment.NewLine + "Payment Violation");
ApplyWrapTextStyleToCell(ws, rowIdx, 15);
ws.SetValue(rowIdx, 16, "Payment Violation" + Environment.NewLine + "Duration");
ApplyWrapTextStyleToCell(ws, rowIdx, 16);
ws.SetValue(rowIdx, 17, "Payment Violation" + Environment.NewLine + "Action Taken");
ApplyWrapTextStyleToCell(ws, rowIdx, 17);
ws.SetValue(rowIdx, 18, "Payment Violation" + Environment.NewLine + "Action Taken Timestamp");
ApplyWrapTextStyleToCell(ws, rowIdx, 18);
Dictionary <int, List <string> > ColumnLinesForRow = new Dictionary <int, List <string> >();
// Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, detailColumnCount])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
// Increment the row index, which will now be the 1st row of our data
rowIdx++;
#region Populate data for each record
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repEvent in this._ReportEngine.ReportDataModel.ReportableEvents)
{
// Ignore unoccupied sections or dummy sensor events
if (repEvent.SensorEvent_IsOccupied == false)
{
continue;
}
if (repEvent.IsDummySensorEvent == true)
{
continue;
}
// The details only need to list records that are involved in either payment or overstay violations (unenforceable sensor and payment events can be ignored)
if ((repEvent.PaymentVios.Count == 0) && (repEvent.Overstays.Count == 0))
{
continue;
}
// Start with fresh collections for each column's text lines for current row
for (int nextCol = 1; nextCol <= detailColumnCount; nextCol++)
{
ColumnLinesForRow[nextCol] = new List <string>();
}
AreaAsset areaAsset = _ReportEngine.GetAreaAsset(repEvent.BayInfo.AreaID_PreferLibertyBeforeInternal);
// Output row values for data
ColumnLinesForRow[1].Add(repEvent.BayInfo.SpaceID.ToString());
ColumnLinesForRow[2].Add(repEvent.BayInfo.MeterID.ToString());
if (areaAsset != null)
{
ColumnLinesForRow[3].Add(areaAsset.AreaID.ToString());
ColumnLinesForRow[4].Add(areaAsset.AreaName);
}
ColumnLinesForRow[5].Add(repEvent.SensorEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[6].Add(repEvent.SensorEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
// Add sensor ins/outs for each "repeat" sensor event
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repeatEvent in repEvent.RepeatSensorEvents)
{
ColumnLinesForRow[5].Add(repEvent.SensorEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[6].Add(repEvent.SensorEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
foreach (SensorAndPaymentReportEngine.OverstayVioEvent overstay in repEvent.Overstays)
{
ColumnLinesForRow[7].Add(overstay.StartOfOverstayViolation.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[8].Add(FormatTimeSpanAsHoursMinutesAndSeconds(overstay.DurationOfTimeBeyondStayLimits));
if (!string.IsNullOrEmpty(overstay.EnforcementActionTaken))
{
ColumnLinesForRow[9].Add(overstay.EnforcementActionTaken);
}
else
{
ColumnLinesForRow[9].Add("");
}
if (overstay.EnforcementActionTakenTimeStamp > DateTime.MinValue)
{
ColumnLinesForRow[10].Add(overstay.EnforcementActionTakenTimeStamp.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
else
{
ColumnLinesForRow[10].Add("");
}
if (overstay.OverstayBasedOnRuleDetail != null)
{
StringBuilder sb = new StringBuilder();
sb.Append(Enum.ToObject(typeof(DayOfWeek), overstay.OverstayBasedOnRuleDetail.DayOfWeek).ToString() + " ");
sb.Append(overstay.OverstayBasedOnRuleDetail.StartTime.ToString("hh:mm:ss tt") + " - " +
overstay.OverstayBasedOnRuleDetail.EndTime.ToString("hh:mm:ss tt") + ", ");
sb.Append(overstay.OverstayBasedOnRuleDetail.Type + ", Max Stay: " + overstay.OverstayBasedOnRuleDetail.MaxStayMinutes.ToString());
ColumnLinesForRow[11].Add(sb.ToString());
}
else
{
ColumnLinesForRow[11].Add("");
}
}
foreach (SensorAndPaymentReportEngine.PaymentEvent payEvent in repEvent.PaymentEvents)
{
if (payEvent.PaymentEvent_IsPaid == false)
{
continue;
}
ColumnLinesForRow[12].Add(payEvent.PaymentEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt"));
if (payEvent.WasStoppedShortViaZeroOutTrans == true)
{
ColumnLinesForRow[13].Add(payEvent.OriginalPaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[14].Add(payEvent.PaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
else
{
ColumnLinesForRow[13].Add(payEvent.PaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[14].Add("");
}
}
foreach (SensorAndPaymentReportEngine.PaymentVioEvent payVio in repEvent.PaymentVios)
{
ColumnLinesForRow[15].Add(payVio.StartOfPayViolation.ToString("yyyy-MM-dd hh:mm:ss tt"));
ColumnLinesForRow[16].Add(FormatTimeSpanAsHoursMinutesAndSeconds(payVio.DurationOfTimeInViolation));
if (!string.IsNullOrEmpty(payVio.EnforcementActionTaken))
{
ColumnLinesForRow[17].Add(payVio.EnforcementActionTaken);
}
else
{
ColumnLinesForRow[17].Add("");
}
if (payVio.EnforcementActionTakenTimeStamp > DateTime.MinValue)
{
ColumnLinesForRow[18].Add(payVio.EnforcementActionTakenTimeStamp.ToString("yyyy-MM-dd hh:mm:ss tt"));
}
else
{
ColumnLinesForRow[18].Add("");
}
}
int linesForRow = 1;
for (int nextCol = 1; nextCol <= detailColumnCount; nextCol++)
{
int columnRowLines = 0;
StringBuilder sb = new StringBuilder();
bool firstLine = true;
foreach (string nextLine in ColumnLinesForRow[nextCol])
{
columnRowLines++;
if (firstLine == false)
{
sb.AppendLine();
}
sb.Append(nextLine);
firstLine = false;
}
ws.SetValue(rowIdx, nextCol, sb.ToString());
if (columnRowLines > linesForRow)
{
linesForRow = columnRowLines;
}
if (columnRowLines > 1)
{
using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, nextCol])
{
ws.Cells[rowIdx, nextCol].Style.WrapText = true;
}
}
}
// Do we need to resize the row?
if (linesForRow > 1)
{
ws.Row(rowIdx).Height = (ws.DefaultRowHeight * linesForRow);
using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, 1, rowIdx, detailColumnCount])
{
rowrange.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
// Increment the row index, which will now be the next row of our data
rowIdx++;
}
#endregion
// We will add autofilters to our headers so user can sort the columns easier
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, rowIdx, detailColumnCount])
{
rng.AutoFilter = true;
}
// Apply formatting to the columns as appropriate (Starting row is 2 (first row of data), and ending row is the current rowIdx value)
// Column 1 & 2 are numeric integer
ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 2, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 3, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 4, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 5, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 6, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 7, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 8, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 9, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 10, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 11, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 12, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 13, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 14, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 15, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 16, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 17, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 18, 2, rowIdx, "@", ExcelHorizontalAlignment.Left);
// And now lets size the columns
for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
}
}
// And now finally we must manually size the columns that have wrap text (autofit doesn't work nicely when we have wrap text)
ws.Column(1 + 6).Width = 24;
ws.Column(1 + 7).Width = 24;
ws.Column(1 + 8).Width = 24;
ws.Column(1 + 9).Width = 27;
ws.Column(1 + 13).Width = 24;
ws.Column(1 + 14).Width = 24;
ws.Column(1 + 15).Width = 24;
ws.Column(1 + 16).Width = 24;
ws.Column(1 + 17).Width = 27;
}
// All cells in spreadsheet are populated now, so render (save the file) to a memory stream
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
// Stop diagnostics timer
sw.Stop();
System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString());
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms,
ActivityRestrictions activityRestriction, string scopedAreaName, string scopedMeter)
{
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
this._ActivityRestriction = activityRestriction;
this.GatherReportData(listOfMeterIDs);
OfficeOpenXml.ExcelWorksheet ws = null;
int rowIdx = -1;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
// Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Summary");
// Render the header row
rowIdx = 1; // Excel uses 1-based indexes
ws.Cells[rowIdx, 1].Value = "Asset Listings Report";
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 1, rowIdx, 10])
{
rng.Merge = true; //Merge columns start and end range
rng.Style.Font.Bold = true;
rng.Style.Font.Italic = true;
rng.Style.Font.Size = 22;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
rowIdx++;
ws.Cells[rowIdx, 1].IsRichText = true;
OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Client: ", this._CustomerConfig.CustomerName);
AddRichTextNameAndValue(rtfCollection, ", Generated: ", NowAtDestination.ToShortDateString() + " " + NowAtDestination.ToShortTimeString());
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
rowIdx++;
rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Included Activity: ", "Asset Listing");
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
if (!string.IsNullOrEmpty(scopedAreaName))
{
rowIdx++;
rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Report limited to area: ", scopedAreaName);
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
}
if (!string.IsNullOrEmpty(scopedMeter))
{
rowIdx++;
rtfCollection = ws.Cells[rowIdx, 1].RichText;
AddRichTextNameAndValue(rtfCollection, "Report limited to meter: ", scopedMeter);
ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true;
}
rowIdx++;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, 10])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
}
rowIdx++;
int hyperlinkstartRowIdx = rowIdx;
rowIdx++;
rowIdx++;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, 13])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
}
// Render the header row
rowIdx = 7; // Excel uses 1-based indexes
// have to start at column 2, does not work when start column is 1. Will come back when more time is avail
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 2, rowIdx, 6])
{
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Merge = true; //Merge columns start and end range
rng.Style.Font.Bold = true;
}
ws.Cells[rowIdx, 2].Value = "Site Details";
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 1, rowIdx, 6])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 7, rowIdx, 13])
{
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Merge = true; //Merge columns start and end range
rng.Style.Font.Bold = true;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237));
}
ws.Cells[rowIdx, 7].Value = "Regulations";
rowIdx++;
ws.Cells[rowIdx, 1].Value = "Meter ID";
ws.Cells[rowIdx, 2].Value = "Space ID";
ws.Cells[rowIdx, 3].Value = "Area #";
ws.Cells[rowIdx, 4].Value = "Site Details Area";
ws.Cells[rowIdx, 5].Value = "Co-Ordinates Lat";
ws.Cells[rowIdx, 6].Value = "Co-Ordinates Long";
ws.Cells[rowIdx, 7].Value = "Regulations - Sun";
ws.Cells[rowIdx, 8].Value = "Regulations - Mon";
ws.Cells[rowIdx, 9].Value = "Regulations - Tues";
ws.Cells[rowIdx, 10].Value = "Regulations - Wed";
ws.Cells[rowIdx, 11].Value = "Regulations - Thurs";
ws.Cells[rowIdx, 12].Value = "Regulations - Fri";
ws.Cells[rowIdx, 13].Value = "Regulations - Sat";
// Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, 6])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
// Increment the row index, which will now be the 1st row of our data
rowIdx++;
foreach (AssetListing_Space meterStat in this._ReportDataModel.SpaceDetailsList)
{
#region Unused code, but useful examples
// Example of how we could automatically render a dataset to worksheet
/*
* // Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
* ws.Cells["A1"].LoadFromDataTable(nextTable, true);
*/
// Example of how we could automatically render a strongly-typed collection of objects to worksheet
/*
* List<MeterStatisticObj> statObjects = new List<MeterStatisticObj>();
* statObjects.Add(meterStatCollection.MeterStats_Summary);
* ws.Cells["A1"].LoadFromCollection(statObjects, true);
*/
// Example of formatting a column for Date/Time
/*
* ws.Column(3).Width = 20;
* using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 3, 2 + nextTable.Rows.Count, 3])
* {
* col.Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss tt";
* col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
* }
*/
// Example of using RichText in a cell for advanced formatting possibilites
/*
* ws.Cells[rowIdx, 1].IsRichText = true;
* ws.Cells[rowIdx, 1].Style.WrapText = true; // Need this if we want multi-line
* OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection = ws.Cells[rowIdx, 1].RichText;
* OfficeOpenXml.Style.ExcelRichText ert = rtfCollection.Add(areaStat.AreaName + "\r\n");
*
* ert = rtfCollection.Add(" (ID=" + areaStat.AreaID.ToString() + ")");
* ert.Bold = false;
* ert.Italic = true;
* ert.Size = 8;
*/
#endregion
// Output row values for data
ws.Cells[rowIdx, 1].Value = meterStat.MeterID;
ws.Cells[rowIdx, 2].Value = meterStat.SpaceID;
ws.Cells[rowIdx, 3].Value = meterStat.AreaID;
ws.Cells[rowIdx, 4].Value = meterStat.Location;
ws.Cells[rowIdx, 5].Value = meterStat.Latitude;
ws.Cells[rowIdx, 6].Value = meterStat.Longitude;
RegulatedHoursGroupRepository.Repository = new RegulatedHoursGroupRepository();
RegulatedHoursGroup regulatedHours = RegulatedHoursGroupRepository.Repository.GetBestGroupForMeter(this._CustomerConfig.CustomerId,
meterStat.AreaID, meterStat.MeterID);
// If no regulated hour defintions came back, then we will default to assumption that regulated period is 24-hours a day
if ((regulatedHours == null) || (regulatedHours.Details == null) || (regulatedHours.Details.Count == 0))
{
rowIdx++;
continue;
}
// Loop through the daily rules and see if the timestamp falls within a Regulated or No Parking timeslot for the appropriate day
foreach (RegulatedHoursDetail detail in regulatedHours.Details)
{
string regulationTxt =
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt") + ", " +
detail.MaxStayMinutes.ToString() + " mins";
if (string.Compare(detail.Type, "Unregulated", true) == 0)
{
regulationTxt = "(Unregulated) " +
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt") + ", " +
detail.MaxStayMinutes.ToString() + " mins";
}
else if (string.Compare(detail.Type, "No Parking", true) == 0)
{
regulationTxt = "(No Parking) " +
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt");
}
else if (detail.MaxStayMinutes < 1)
{
regulationTxt = "(No Limit) " +
detail.StartTime.ToString("hh:mm:ss tt") + " - " +
detail.EndTime.ToString("hh:mm:ss tt");
}
// Determine which column of the spreadsheet is used for this day of the week
int columnIdxForDayOfWeek = 7 + detail.DayOfWeek;
// If the cell is empty, just add the regulation text. If something is already there, append the regulation text
// (There might be more than one regulated period for the same day)
if ((ws.Cells[rowIdx, columnIdxForDayOfWeek].Value == null) || ((ws.Cells[rowIdx, columnIdxForDayOfWeek].Value as string) == null))
{
ws.Cells[rowIdx, columnIdxForDayOfWeek].Value = regulationTxt;
}
else
{
ws.Cells[rowIdx, columnIdxForDayOfWeek].Value = (ws.Cells[rowIdx, columnIdxForDayOfWeek].Value as string) + Environment.NewLine + regulationTxt;
// And increment the row height also
ws.Row(rowIdx).Height = ws.Row(rowIdx).Height + ws.DefaultRowHeight;
ws.Cells[rowIdx, columnIdxForDayOfWeek].Style.WrapText = true;
using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, 1, rowIdx, 14])
{
rowrange.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
}
// Increment the row index, which will now be the next row of our data
rowIdx++;
}
// We will add autofilters to our headers so user can sort the columns easier
using (OfficeOpenXml.ExcelRange rng = ws.Cells[8, 1, 8, 13])
{
rng.AutoFilter = true;
}
// Column 1 is numeric integer (Meter ID)
ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
// And now lets size the columns
for (int autoSizeColIdx = 1; autoSizeColIdx <= 13; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
}
}
// All cells in spreadsheet are populated now, so render (save the file) to a memory stream
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
// Stop diagnostics timer
sw.Stop();
System.Diagnostics.Debug.WriteLine("Occupancy Report Generation took: " + sw.Elapsed.ToString());
}
protected override void RenderCommonData(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory, GroupStats statsObj)
{
int colIdx_1stCommonColumn = startColIdx;
int renderColIdx = colIdx_1stCommonColumn;
int renderRowIdx = startRowIdx;
ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalOccupancyTime));
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalOccupancyPaidTime));
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.MaximumPotentialOccupancyTime));
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.PercentageOccupiedPaid);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.PercentageOccupiedNotPaid);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.ingress);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.egress);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.PaymentCount);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalZeroedOutEvents);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalZeroedOutDuration));
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosActioned);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.PayVioCount);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosEnforced);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosCautioned);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosNotEnforced);
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosFaulty);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 3, renderRowIdx, renderRowIdx, "###0.00", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 4, renderRowIdx, renderRowIdx, "###0.00", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 5, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 6, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 7, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 8, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 10, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 11, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 12, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 13, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 14, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 15, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right);
// And now lets autosize the columns
for (int autoSizeColIdx = colIdx_1stCommonColumn; autoSizeColIdx <= renderColIdx; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, renderRowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
col.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
// And now finally we must manually size the columns that have wrap text (autofit doesn't work nicely when we have wrap text)
ws.Column(colIdx_1stCommonColumn + 0).Width = 15;
ws.Column(colIdx_1stCommonColumn + 1).Width = 20;
ws.Column(colIdx_1stCommonColumn + 2).Width = 20;
ws.Column(colIdx_1stCommonColumn + 3).Width = 20;
ws.Column(colIdx_1stCommonColumn + 4).Width = 24;
ws.Column(colIdx_1stCommonColumn + 5).Width = 12;
ws.Column(colIdx_1stCommonColumn + 6).Width = 12;
ws.Column(colIdx_1stCommonColumn + 8).Width = 15;
ws.Column(colIdx_1stCommonColumn + 9).Width = 15;
ws.Column(colIdx_1stCommonColumn + 10).Width = 12;
ws.Column(colIdx_1stCommonColumn + 11).Width = 12;
ws.Column(colIdx_1stCommonColumn + 12).Width = 12;
ws.Column(colIdx_1stCommonColumn + 13).Width = 12;
ws.Column(colIdx_1stCommonColumn + 14).Width = 12;
ws.Column(colIdx_1stCommonColumn + 15).Width = 12;
ws.Column(colIdx_1stCommonColumn + 16).Width = 40;
if (this._ReportParams.IncludeHourlyStatistics == true)
{
// Now we will construct the hourly category column, followed by hour detail columns
ws.SetValue(renderRowIdx + 0, colIdx_HourlyCategory, "Occupied Duration");
ws.SetValue(renderRowIdx + 1, colIdx_HourlyCategory, "Occupied & Paid Duration");
ws.SetValue(renderRowIdx + 2, colIdx_HourlyCategory, "Max Possible Duration");
ws.SetValue(renderRowIdx + 3, colIdx_HourlyCategory, "Occupied & Paid % (Compliance)");
ws.SetValue(renderRowIdx + 4, colIdx_HourlyCategory, "Occupied & Not Paid % (Non-Compliance");
ws.SetValue(renderRowIdx + 5, colIdx_HourlyCategory, "Arrivals");
ws.SetValue(renderRowIdx + 6, colIdx_HourlyCategory, "Departures");
ws.SetValue(renderRowIdx + 7, colIdx_HourlyCategory, "Payment Count");
ws.SetValue(renderRowIdx + 8, colIdx_HourlyCategory, "Zeroed Out Events");
ws.SetValue(renderRowIdx + 9, colIdx_HourlyCategory, "Total Zeroed Out Time");
ws.SetValue(renderRowIdx + 10, colIdx_HourlyCategory, "Violations Actioned");
ws.SetValue(renderRowIdx + 11, colIdx_HourlyCategory, "Total Violations");
ws.SetValue(renderRowIdx + 12, colIdx_HourlyCategory, "Total Enforced");
ws.SetValue(renderRowIdx + 13, colIdx_HourlyCategory, "Total Cautioned");
ws.SetValue(renderRowIdx + 14, colIdx_HourlyCategory, "Total Not Enforced");
ws.SetValue(renderRowIdx + 15, colIdx_HourlyCategory, "Total Faulty");
using (OfficeOpenXml.ExcelRange col = ws.Cells[renderRowIdx, colIdx_HourlyCategory, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory])
{
col.Style.Font.Bold = true;
}
MergeCellRange(ws, renderRowIdx + 1, 1, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory - 1);
}
}
