/// <summary>
/// Manages the field report import and processing functions.
/// </summary>
private void RunFieldReport()
{
//Validate and assign the job number before opening the file dialog window. Validation is
//performed by the JobNumberValidation object upon instantiation.
JobNumberValidation validationControl = new JobNumberValidation(txtJobNumber.Text, IsSaveOperation: false);
if (validationControl.IsValidJobNumber && validationControl.HasEstimateData(validationControl.ValidationResult))
{
//Grab the validated job number.
string jobNumber = txtJobNumber.Text;
OpenFileDialog ImportFieldReportDialog = new OpenFileDialog()
{
Filter = "CSV Files (*.csv)|*.csv",
Title = "Select a Field Report"
};
DialogResult importResult = ImportFieldReportDialog.ShowDialog();
if (importResult == DialogResult.OK)
{
// MAIN PROGRAM
// ***************************************************
//1. READ THE CSV FILE INTO MEMORY.
//Get the selected file path.
string selectedFile = ImportFieldReportDialog.FileName;
// Use the CSVHelper to process the file contents and produce a list of SystemReport objects.
CSVHelper csvHelper = new CSVHelper();
List <SystemReport> reportedSystems = csvHelper.GetReportedSystemList(selectedFile);
if (reportedSystems != null || reportedSystems.Count != 0)
{
// ***************************************************
//2. USE THE CLIENT REPORT SERVICE TO RUN AND RETURN THE REPORT OBJECT.
//Note: To get this far, the field report must have already been validated and processed into a List of SystemReport objects.
//Create the report service.
ClientReportService clientReportService = new ClientReportService(jobNumber, _dataProvider);
//Send the field report system list to the report service to generate the full summary report object.
//ComparatorReport finishedReport = clientReportService.GetReportSummary(reportedSystems);
CostCodeReport costCodeReport = clientReportService.GetCostCodeReportSummary(reportedSystems);
// ***************************************************
//3. DISPLAY THE PREFERRED DATA FROM THE REPORT OBJECT.
//Send the report object to the data display service.
DataDisplayService dataDisplayService = new DataDisplayService();
dataDisplayService.DisplayCostCodeReport(costCodeReport);
}
else
{
MessageBox.Show("No system data was found in the report. Please check the imported file and verify that Phase Code and Equipment System data are present in the file before trying again. If problem persists, please contact Technical Support.", "No Data Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
throw new Exception("Reporting service returned a blank or null system report list to the Main program without throwing a validation error. Please check the CSVHelper class to make sure that type conversions are working properly.");
}
}
}
else
{
//throw new Exception("Validation Failed");
}
}
/// <summary>
///
/// </summary>
/// <param name="jobNumber"></param>
public void GenerateCostCodeReport(string jobNumber)
{
if (_reportModel != null && _dataProvider != null)
{
try
{
CostCodeReport costCodeReport = new CostCodeReport(jobNumber, _reportModel, _dataProvider);
costCodeReport.Generate();
CostCodeReport = costCodeReport;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
//public static DataDisplayService LoadDataObject(ComparatorReport comparatorReport)
//{
// DataDisplayService dataDisplayService = new DataDisplayService();
// dataDisplayService._comparatorReport = comparatorReport;
// return dataDisplayService;
//}
public void DisplayCostCodeReport(CostCodeReport costCodeReport)
{
if (costCodeReport != null)
{
//Workbook newWorkbook = Globals.ThisAddIn.Application.Workbooks.Add();
Excel = Globals.ThisAddIn.Application;
Excel.Visible = true;
Excel.DisplayAlerts = false;
//Get a new workbook and add a single sheet. The new sheet will automatically become the activesheet.
reportWorkbook = (_Workbook)(Excel.Workbooks.Add());
//reportWorksheet = (_Worksheet)reportWorkbook.Worksheets.Add();
reportWorksheet = reportWorkbook.ActiveSheet;
//Set colors
InitializeColorScheme();
//Get table headers.
List <string> costReportHeaders = GenerateCostReportHeaders();
//Write the table headers to the active worksheet
for (int i = 1; i <= costReportHeaders.Count; i++)
{
reportWorksheet.Cells[headerRow, i].Value = costReportHeaders[i - 1];
reportWorksheet.Cells[headerRow, i].Font.Bold = true;
reportWorksheet.Cells[headerRow, i].Font.Size = 14;
if (i <= 4)
{
reportWorksheet.Cells[headerRow, i].Interior.Color = BudgetHeaderColor;
reportWorksheet.Cells[headerRow, i].Font.Color = XlRgbColor.rgbWhite;
}
else
{
reportWorksheet.Cells[headerRow, i].Interior.Color = ActualHeaderColor;
//reportWorksheet.Cells[headerRow, i].Font.Color = XlRgbColor.rgbDarkGrey;
reportWorksheet.Cells[headerRow, i].Font.Color = XlRgbColor.rgbWhite;
}
}
//Initialize the incremental variable used for writing records to the worksheet.
int recordRow = headerRow + 1;
foreach (CostCodeResult cc in costCodeReport.CostCodeResults)
{
//Select the entire row for formatting
worksheetRange = reportWorksheet.Range[reportWorksheet.Cells[recordRow, 1], reportWorksheet.Cells[recordRow, 12]];
worksheetRange.Font.Size = 14;
worksheetRange.Font.Bold = false;
//Color the Budget area.
Range budgetedValuesRange = reportWorksheet.Range[reportWorksheet.Cells[recordRow, 1], reportWorksheet.Cells[recordRow, 4]];
budgetedValuesRange.Interior.Color = BudgetRecordColor;
//Color the Actual area.
Range actualValuesRange = reportWorksheet.Range[reportWorksheet.Cells[recordRow, 5], reportWorksheet.Cells[recordRow, 12]];
actualValuesRange.Interior.Color = ActualRecordColor;
//BUDGETED
//Activity-PhaseCode
reportWorksheet.Cells[recordRow, 1] = cc.PhaseCode;
//Units (Budgeted)
reportWorksheet.Cells[recordRow, 2] = cc.EstimatedUnitQuantity;
//UOM (type)
reportWorksheet.Cells[recordRow, 3] = GetUnitOfMeasurement(cc.UnitOfMeasurement);
//Hours (Budgeted)
reportWorksheet.Cells[recordRow, 4] = cc.ProjectedHours;
//ACTUAL
//Units (Actual)
reportWorksheet.Cells[recordRow, 5] = cc.ActualUnitQuantity;
//UOM (Actual)
reportWorksheet.Cells[recordRow, 6] = GetUnitOfMeasurement(cc.UnitOfMeasurement);
//% Complete
reportWorksheet.Cells[recordRow, 7] = cc.PercentComplete;
reportWorksheet.Cells[recordRow, 7].NumberFormat = "0%";
//Team Budget
reportWorksheet.Cells[recordRow, 8] = cc.BudgetedHours;
//Earned Hrs
reportWorksheet.Cells[recordRow, 9] = cc.EarnedHours;
//Actual Hrs
reportWorksheet.Cells[recordRow, 10] = cc.ActualHours;
//Earned/Actual
reportWorksheet.Cells[recordRow, 11] = cc.EarnedActualRatio;
//Apply conditional formatting
//Color the Actual area.
Range performanceValuesRange = reportWorksheet.Range[reportWorksheet.Cells[recordRow, 11], reportWorksheet.Cells[recordRow, 11]];
if (cc.EarnedActualRatio > 1)
{
performanceValuesRange.Interior.Color = OverProjection;
}
else
{
performanceValuesRange.Interior.Color = UnderProjection;
}
//Projected Hours
reportWorksheet.Cells[recordRow, 12] = Math.Round((cc.BudgetedHours * cc.EarnedActualRatio), 2);
//Hours (Projected)
//TODO: Discuss calculation with Grant
//Increment the counter
recordRow += 1;
}
//Create the merged cell headers; this can only happen if (headerRow >= 2).
if (headerRow >= 2)
{
//BUDGET
Range BudgetValuesHeaderRange = reportWorksheet.Range[reportWorksheet.Cells[headerRow - 1, 1], reportWorksheet.Cells[headerRow - 1, 4]];
BudgetValuesHeaderRange.Value = "Budget";
BudgetValuesHeaderRange.Merge();
BudgetValuesHeaderRange.Interior.Color = ColorTranslator.FromHtml("#e3e3e3");
BudgetValuesHeaderRange.Font.Size = FontSizeHeader;
BudgetValuesHeaderRange.RowHeight = RowHeightHeader;
BudgetValuesHeaderRange.Font.Bold = true;
BudgetValuesHeaderRange.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//ACTUAL
Range ActualValuesHeaderRange = reportWorksheet.Range[reportWorksheet.Cells[headerRow - 1, 5], reportWorksheet.Cells[headerRow - 1, 10]];
ActualValuesHeaderRange.Value = "Actual";
ActualValuesHeaderRange.Merge();
ActualValuesHeaderRange.Interior.Color = ColorTranslator.FromHtml("#e3e3e3");
ActualValuesHeaderRange.Font.Size = FontSizeHeader;
ActualValuesHeaderRange.RowHeight = RowHeightHeader;
ActualValuesHeaderRange.Font.Bold = true;
ActualValuesHeaderRange.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//PRODUCTIVITY
reportWorksheet.Cells[headerRow - 1, 11] = "Productivity";
reportWorksheet.Cells[headerRow - 1, 11].Interior.Color = ColorTranslator.FromHtml("#e3e3e3");
reportWorksheet.Cells[headerRow - 1, 11].Font.Size = FontSizeHeader;
reportWorksheet.Cells[headerRow - 1, 11].Font.Bold = true;
reportWorksheet.Cells[headerRow - 1, 11].RowHeight = RowHeightHeader;
reportWorksheet.Cells[headerRow - 1, 11].Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//PROJECTION
reportWorksheet.Cells[headerRow - 1, 12] = "Projection";
reportWorksheet.Cells[headerRow - 1, 12].Interior.Color = ColorTranslator.FromHtml("#e3e3e3");
reportWorksheet.Cells[headerRow - 1, 12].Font.Size = FontSizeHeader;
reportWorksheet.Cells[headerRow - 1, 12].Font.Bold = true;
reportWorksheet.Cells[headerRow - 1, 12].RowHeight = RowHeightHeader;
reportWorksheet.Cells[headerRow - 1, 12].Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
//Autofit all cells with content.
reportWorksheet.Columns.AutoFit();
//Hide the columns we don't want to show: B, C, E, F
reportWorksheet.Range["B:B"].EntireColumn.Hidden = true;
reportWorksheet.Range["C:C"].EntireColumn.Hidden = true;
reportWorksheet.Range["E:E"].EntireColumn.Hidden = true;
reportWorksheet.Range["F:F"].EntireColumn.Hidden = true;
//Add the chart
// GenerateChart();
}
}
