/// <summary>
/// Create the Report
/// </summary>
/// <param name="usersBaseReportingDirectory">Filename to save Excel file to.</param>
public string Create(string usersBaseReportingDirectory)
{
if (string.IsNullOrEmpty(usersBaseReportingDirectory)) throw new ArgumentNullException("usersBaseReportingDirectory");
if (!Directory.Exists(usersBaseReportingDirectory))
{
Directory.CreateDirectory(usersBaseReportingDirectory);
}
string fileName = Path.Combine(usersBaseReportingDirectory, "OverrideSummary_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls");
using (CmsEntities cee = new CmsEntities())
{
cee.Configuration.LazyLoadingEnabled = true;
IList<IssueRelatedOverride> issueRelatedOverrides;
if (IncludeRemovedOverrides)
{
// Include both removed and not removed overrides in the report
issueRelatedOverrides = (from iro in cee.IssueRelatedOverrides
orderby iro.IssueId
select iro).ToList();
}
else
{
// Include only not removed overrides in the report
issueRelatedOverrides = (from iro in cee.IssueRelatedOverrides
where iro.Removed == false
orderby iro.IssueId
select iro).ToList();
}
using (ExcelXmlWriter excel = new ExcelXmlWriter(fileName, false))
{
#region Style
string[] Styles =
{
"<Style ss:ID=\"Border\">",
" <Alignment ss:Vertical=\"Top\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreBold\">",
" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentre\">",
" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreRed\">",
" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
" <Interior ss:Color=\"#FF0000\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreH\">",
" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Top\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreLeft\">",
" <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
"</Style>",
"<Style ss:ID=\"BorderWrapTextCentreV\">",
" <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
"</Style>",
"<Style ss:ID=\"BorderWrapText\">",
" <Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\"/>",
"</Style>",
"<Style ss:ID=\"BorderBold\">",
" <Alignment ss:Vertical=\"Top\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
"</Style>",
"<Style ss:ID=\"BorderGray\">",
" <Alignment ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
" <Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderLightGray\">",
" <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
" <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderLightGrayCentre\">",
" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
" <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreBoldNR\">",
" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Times New Roman\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreWingdings2\">",
" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>",
" <Borders>",
" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>",
" </Borders>",
" <Font ss:FontName=\"Wingdings 2\" x:CharSet = \"2\" x:Family=\"Roman\" ss:Size=\"9\" ss:Bold=\"1\"/>",
"</Style>"
};
#endregion
excel.WriteHeader(Styles);
_pageBreakRows.Clear();
excel.Landscape = true;
excel.PrintScale = 100;
//excel.PaperSize = 8; // A3
excel.SetPageMargins(0.5, 0.5, 0.5, 0.5);
excel.OpenWorkSheet("Override Summary");
excel.PrintTitle(1, 1);
double[] colWidths = new[]
{
52.5, //Issue ID
76.5, //Element Tag
130, //Description
69, //Interlock Type
69, //Interlock No.
130, //Interlock Description
118.5, //Interlock Cause
118.5, //Approval (Area Engineer / Tech)
118.5,//"Approval (Lead Engineer / Supervisor)
118.5,//"Approval (Manager)
69, //Assigned To
118.5,//Applied
118.5, //Target Removal
118.5, //Removed
130 //Notes
};
excel.OpenTable(200, colWidths.Length, colWidths);
int row = 1;
excel.StartTitle();
excel.OpenRow(row++, 20);
excel.WriteCell(new ExcelValueObject("Issue ID", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Element Tag", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Description", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Interlock Type", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Interlock No.", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Interlock Description", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Interlock Cause", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Approval (Area Engineer / Tech)", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Approval (Lead Engineer / Supervisor)", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Approval (Manager)", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Assigned To", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Applied", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Target Removal", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Removed", ExcelCellFormat.String, "BorderLightGray"));
excel.WriteCell(new ExcelValueObject("Notes", ExcelCellFormat.String, "BorderLightGray"));
excel.CloseRow();
excel.EndTitle();
foreach (IssueRelatedOverride relatedOverride in issueRelatedOverrides)
{
excel.OpenRow(row++, 17);
string intDescription, number, type, xtag, desc, cause;
int ControlSystemId;
int? dummy;
int interlockId = relatedOverride.InterlockId;
CmsWebService.GetInterlockInfo(interlockId, out ControlSystemId, out intDescription, out number, out type, out dummy, out cause, _artDbConnectionString);
CmsWebService.GetControlSystemInfo(ControlSystemId, out xtag, out desc, _artDbConnectionString);
excel.WriteCell(new ExcelValueObject(relatedOverride.IssueId.ToString(), ExcelCellFormat.Number, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(xtag, ExcelCellFormat.String, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(desc, ExcelCellFormat.String, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(type, ExcelCellFormat.String, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(number, ExcelCellFormat.Number, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(intDescription, ExcelCellFormat.String, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(cause, ExcelCellFormat.String, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(CmsWebService.GetUserName(relatedOverride.Issue.CurrentlyAssignedToId, _issueDbConnectionString), ExcelCellFormat.String, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(CmsWebService.GetTimestampUserString(relatedOverride.AppliedDate, relatedOverride.AppliedById, _issueDbConnectionString),
ExcelCellFormat.String, "BorderCentre"));
// Highlight an override that's overdue for removal
string targetRemovalStyle = "BorderCentre";
if (relatedOverride.TargetRemovalDate.HasValue)
{
if (DateTime.Now.Date > relatedOverride.TargetRemovalDate.Value.Date && !relatedOverride.Removed)
{
targetRemovalStyle = "BorderCentreRed";
}
}
excel.WriteCell(new ExcelValueObject(CmsWebService.GetDateString(relatedOverride.TargetRemovalDate), ExcelCellFormat.String, targetRemovalStyle));
excel.WriteCell(new ExcelValueObject(CmsWebService.GetTimestampUserString(relatedOverride.RemovedDate, relatedOverride.RemovedById, _issueDbConnectionString),
ExcelCellFormat.String, "BorderCentre"));
excel.WriteCell(new ExcelValueObject(relatedOverride.Notes, ExcelCellFormat.String, "BorderWrapTextCentreV"));
excel.CloseRow();
}
excel.CloseTable();
excel.WritePageBreaks(_pageBreakRows.ToArray());
excel.CloseWorkSheet();
excel.Close();
}
}
return fileName;
}
