/// <summary>
/// Create the Report
/// </summary>
/// <param name="usersBaseReportingDirectory">The file path.</param>
/// <returns></returns>
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, "Issues_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls");
using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true })
{
using (CmsEntities cee = new CmsEntities())
{
cee.Configuration.LazyLoadingEnabled = true;
IList<Issue> issues;
List<int> ids = new List<int>();
ids.Add(1);
if (System.Diagnostics.Debugger.IsAttached)
{
issues = (from x in cee.Issues select x).Take(20).ToList();
}
else
{
issues = (from x in cee.Issues select x).ToList();
}
CreateIssuesSummaryWorksheet(excelXmlWriter, issues);
CreateActionsSummaryWorksheet(excelXmlWriter, issues);
CreateMobilePlantIssuesWorksheet(excelXmlWriter, issues);
CreateMechEquipWorksheet(excelXmlWriter, issues);
CreateElectEquipWorksheet(excelXmlWriter, issues);
CreatePipingIssuesWorksheet(excelXmlWriter, issues);
CreateDocumentationWorksheet(excelXmlWriter, issues);
excelXmlWriter.Close();
}
}
return fileName;
}
/// <summary>
/// Create the Report
/// </summary>
/// <param name="fileName">Filename to save Excel file to.</param>
/// <param name="fdsId">The FDS id.</param>
/// <param name="fdsName">Name of the FDS.</param>
public void Create(string fileName, int fdsId, string fdsName)
{
ExcelXmlWriter excel = new ExcelXmlWriter(fileName, false);
#region HardCoded Styles
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=\"8\"/>",
"</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=\"8\" 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=\"8\"/>",
"</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=\"8\"/>",
"</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=\"8\"/>",
"</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=\"8\"/>",
"</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=\"8\"/>",
"</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=\"8\" 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=\"8\" ss:Bold=\"1\"/>",
" <Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderLightGray\">",
" <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=\"8\" 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=\"8\" ss:Bold=\"1\"/>",
" <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderLightGrayCentreWrap\">",
" <Alignment ss:Horizontal=\"Center\" 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=\"8\" ss:Bold=\"1\"/>",
" <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>",
"</Style>",
// Colours
"<Style ss:ID=\"BorderCentreCyan\">",
" <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=\"8\"/>",
" <Interior ss:Color=\"#00FFFF\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreGrey\">",
" <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=\"8\"/>",
" <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreMagenta\">",
" <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=\"8\"/>",
" <Interior ss:Color=\"#FF00FF\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreBlack\">",
" <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=\"8\" ss:Color=\"#FFFFFF\"/>",
" <Interior ss:Color=\"#000000\" ss:Pattern=\"Solid\"/>",
"</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=\"8\"/>",
" <Interior ss:Color=\"#FF0000\" ss:Pattern=\"Solid\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreWhite\">",
" <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=\"8\"/>",
"</Style>",
"<Style ss:ID=\"BorderCentreYellow\">",
" <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=\"8\"/>",
" <Interior ss:Color=\"#FFFF00\" ss:Pattern=\"Solid\"/>",
"</Style>",
};
#endregion
excel.WriteHeader(Styles);
excel.Close();
}
internal string CreateOverdues(string usersBaseReportingDirectory)
{
if (String.IsNullOrEmpty(usersBaseReportingDirectory)) throw new ArgumentNullException("usersBaseReportingDirectory");
if (!Directory.Exists(usersBaseReportingDirectory))
{
Directory.CreateDirectory(usersBaseReportingDirectory);
}
string fileName = Path.Combine(usersBaseReportingDirectory, "Overdue_Actions_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls");
using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true })
{
using (CmsEntities cee = new CmsEntities())
{
cee.Configuration.LazyLoadingEnabled = true;
List<int> ids = new List<int>();
ids.Add(1);
IList<Issue> issues = (from x in cee.Issues
.Include("IssueType")
.Include("IssueClassification")
.Include("IssuePriority")
.Include("IssueStatus")
.Include("CurrentlyAssignedToUser")
.Include("ModifiedByUser")
.Include("InitiatedByUser")
.Include("IssueRelatedPipes")
from relatedAction in x.IssueRelatedActions
where relatedAction.RequiredDate.HasValue && relatedAction.RequiredDate.Value < DateTime.Now
select x).ToList();
CreateIssuesSummaryWorksheet(excelXmlWriter, issues);
CreateActionsSummaryWorksheet(excelXmlWriter, issues);
CreateMobilePlantIssuesWorksheet(excelXmlWriter, issues);
CreateMechEquipWorksheet(excelXmlWriter, issues);
CreateElectEquipWorksheet(excelXmlWriter, issues);
CreatePipingIssuesWorksheet(excelXmlWriter, issues);
CreateDocumentationWorksheet(excelXmlWriter, issues);
excelXmlWriter.Close();
}
}
return fileName;
}
/// <summary>
/// Create the Report
/// </summary>
/// <param name="issueIdList">The issue id list.</param>
/// <param name="filePath">The file path.</param>
/// <returns></returns>
public string Create(int[] issueIdList, string filePath)
{
if (issueIdList == null) throw new ArgumentNullException("issueIdList");
if (String.IsNullOrEmpty(filePath)) throw new ArgumentNullException("filePath");
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
string fileName = Path.Combine(filePath, "FilteredIssues_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls");
using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true })
{
using (CmsEntities cee = new CmsEntities())
{
cee.Configuration.LazyLoadingEnabled = true;
IList<Issue> issues;
if (System.Diagnostics.Debugger.IsAttached)
{
issues = (from x in cee.Issues where issueIdList.Contains(x.Id) select x).Take(20000).ToList();
}
else
{
issues = (from x in cee.Issues where issueIdList.Contains(x.Id) select x).ToList();
}
CreateIssuesSummaryWorksheet(excelXmlWriter, issues);
CreateActionsSummaryWorksheet(excelXmlWriter, issues);
CreateMobilePlantIssuesWorksheet(excelXmlWriter, issues);
CreateMechEquipWorksheet(excelXmlWriter, issues);
CreateElectEquipWorksheet(excelXmlWriter, issues);
CreatePipingIssuesWorksheet(excelXmlWriter, issues);
CreateDocumentationWorksheet(excelXmlWriter, issues);
excelXmlWriter.Close();
}
}
return fileName;
}
private string RunExport(string usersBaseReportingDirectory, IEnumerable<Pipe> pipes)
{
string fileName = Path.Combine(usersBaseReportingDirectory, "Pipes_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls");
using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true })
{
using (CmsEntities cee = new CmsEntities())
{
List<int> ids = new List<int>();
ids.Add(1);
List<Document> documents = cee.Documents.ToList();
CreateWorksheet(excelXmlWriter, pipes, documents);
excelXmlWriter.Close();
}
}
return fileName;
}
/// <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;
}
private string RunExport(CmsEntities cee, string usersBaseReportingDirectory, IList<PipeComponent> allPipeComponents)
{
string fileName = Path.Combine(usersBaseReportingDirectory, "PipeComponents_" + DateTime.Now.ToString("dd_MM_yyyy_hhmm") + ".xls");
using (ExcelXmlWriter excelXmlWriter = new ExcelXmlWriter(fileName) { Landscape = true })
{
List<int> ids = new List<int>();
ids.Add(1);
IList<PipeComponentType> allPipeComponentTypes = (from x in cee.PipeComponentTypes select x).ToList();
IList<PipeProperty> allComponentProperties = (from p in cee.PipeProperties select p).ToList();
foreach (PipeComponentType componentType in allPipeComponentTypes)
{
IList<PipeComponent> pipeComponents = (from x in allPipeComponents where x.PipeComponentType.Id == componentType.Id select x).ToList();
if (pipeComponents.Count > 0)
{
IList<PipeProperty> componentProperties = new List<PipeProperty>();
int[] componentpropertyIds = (from x in cee.PipeComponentTypeProperties where x.ComponentTypeId == componentType.Id select x.PipePropertyId).ToArray();
if (componentpropertyIds.Length > 0)
{
componentProperties = (from p in allComponentProperties where componentpropertyIds.Contains(p.Id) select p).ToList();
}
CreateWorksheet(componentType.Name, excelXmlWriter, pipeComponents, componentProperties);
}
}
excelXmlWriter.Close();
}
return fileName;
}
