private void CreateMobilePlantIssuesWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
{
excelXmlWriter.OpenWorkSheet("Issues - Mobile Plant");
double[] colWidths = new[]
{
80d, 110d, 80d, 220d, 200d,
75d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "MobilePlant", "Mobile Plant Description", "Implemented",
"Tested"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in issues)
{
foreach (IssueRelatedMobilePlant relatedMobilePlant in issue.IssueRelatedMobilePlants)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1 CMS Issue ID
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2 CMS Issue Title
values.Add(new ExcelValueObject(issue.Name));
//3 CP ID
values.Add(new ExcelValueObject(relatedMobilePlant.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//4 CP Desc
values.Add(new ExcelValueObject(relatedMobilePlant.MobilePlant.Description));
//5 implemented Yes/No
if (relatedMobilePlant.Implemented)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
//6 tested
if (relatedMobilePlant.Tested)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
excelXmlWriter.WriteRow(row++, values.ToArray());
}
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreatePipingIssuesWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
{
excelXmlWriter.OpenWorkSheet("Issues - Piping");
double[] colWidths = new[]
{
80d, 110d, 80d, 220d, 200d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "Pipe", "Implemented","Tested"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in issues)
{
foreach (IssueRelatedPipe issueRelatedPipe in issue.IssueRelatedPipes)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1 CMS Issue ID
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2 CMS Issue Title
values.Add(new ExcelValueObject(issue.Name));
//3 PIPE ID
values.Add(new ExcelValueObject(issueRelatedPipe.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//5 implemented Yes/No
if (issueRelatedPipe.Implemented)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
//6 TESTED
if (issueRelatedPipe.Tested)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
excelXmlWriter.WriteRow(row++, values.ToArray());
}
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreateMechEquipWorksheet(ExcelXmlWriter excel, IEnumerable<Issue> matchList)
{
excel.OpenWorkSheet("Issues - Mech Equipment");
double[] colWidths = new[]
{
30d, 180d, 150d, 150d, 150d,
100d, 100d
};
excel.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "Mech Equipment", "Mech Equipment Description", "Maint Sys ID",
"Implemented", "Tested"
};
excel.WriteRow(row++, header, true);
using (CmsEntities cee = new CmsEntities())
{
cee.Configuration.LazyLoadingEnabled = true;
foreach (Issue issue in matchList)
{
var rsq = from rs in cee.IssueRelatedMechanicalEquipments
where rs.IssueId == issue.Id
select rs;
foreach (IssueRelatedMechanicalEquipment rs in rsq.ToList())
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2
values.Add(new ExcelValueObject(issue.Name));
//3
values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.Name : ""));
//4
values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.Description : ""));
//5
values.Add(new ExcelValueObject(rs.MechanicalEquipment != null ? rs.MechanicalEquipment.MaintSystemId : ""));
//6
values.Add(new ExcelValueObject(CmsWebService.GetTimestampUserString(rs.ImplementedDate, rs.ImplementedById)));
//7
values.Add(new ExcelValueObject(CmsWebService.GetTimestampUserString(rs.TestedDate, rs.TestedById)));
excel.WriteRow(row++, values.ToArray());
}
}
}
excel.CloseTable();
excel.CloseWorkSheet();
}
private void CreateIssuesSummaryWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> matchList)
{
excelXmlWriter.OpenWorkSheet("Issues - Summary");
double[] colWidths = new[]
{
30d, 150d, 150d, 150d, 150d,
75d, 100d, 100d, 75d, 75d,
75d, 75d, 75d, 110d, 110d,
110d, 110d, 180d, 180d, 180d,
165d, 165d, 165d, 165d, 165d,
165d, 90d, 90d, 90d, 90d,110d,
110d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"ID", "Title", "Description", "Reason", "Suggested Solution",
"Status", "Priority", "Classification", "Issue Type", "Risk Rating",
"Category", "Assigned To", "Initiated By", "Initiated On",
"Requested By", "Designed By", "Approval",
"Commissioning Engineer Approval", "Operations Engineer Approval",
"Estimated Implementation (Hrs)", "Actual Implementation (Hrs)",
"Estimated Testing (Hrs)", "Actual Testing (Hrs)", "Estimated Documentation (Hrs)",
"Actual Documentation (Hrs)", "Implemented", "Tested", "Documented", "Actions",
"Last Modified By",
"Last Modified On"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in matchList)
{
CmsWebService.IssueSummaryTotals totals = CmsWebService.GetIssuesSummaryTotals(issue.Id);
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2
values.Add(new ExcelValueObject(issue.Name));
//3
values.Add(new ExcelValueObject(issue.Description));
//4
values.Add(new ExcelValueObject(issue.Reason));
//5
values.Add(new ExcelValueObject(issue.SuggestedSolution));
//6
values.Add(new ExcelValueObject(CmsWebService.GetStatusName(issue.IssueStatusId)));
//7
values.Add(new ExcelValueObject(CmsWebService.GetPriorityName(issue.IssuePriorityId)));
//8
values.Add(new ExcelValueObject(CmsWebService.GetClassificationName(issue.IssueClassificationId)));
//9
values.Add(new ExcelValueObject(CmsWebService.GetIssueTypeName(issue.IssueTypeId)));
//10
values.Add(new ExcelValueObject(CmsWebService.GetFinalRiskRatingText(issue.Id)));
//11
values.Add(new ExcelValueObject(CmsWebService.GetAssignedCategoryNames(issue)));
//12
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.CurrentlyAssignedToId)));
//13
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.InitiatedById)));
//14
values.Add(new ExcelValueObject(issue.InitiatedDate.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
//15
values.Add(new ExcelValueObject(issue.RequestedBy));
//16
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.ProjectSupervisorId)));
//17
values.Add(new ExcelValueObject(GetApproversText(issue)));
//18
values.Add(new ExcelValueObject(String.Empty));//now redundant
//19
values.Add(new ExcelValueObject(String.Empty));
//20
if (issue.IssueTracking.ImplementedEstimate != null)
values.Add(new ExcelValueObject(issue.IssueTracking.ImplementedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//21
if (issue.IssueTracking.ImplementedActual != null)
values.Add(new ExcelValueObject(issue.IssueTracking.ImplementedActual.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//22
if (issue.IssueTracking.TestedEstimate != null)
values.Add(new ExcelValueObject(issue.IssueTracking.TestedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//23
if (issue.IssueTracking.TestedActual != null)
values.Add(new ExcelValueObject(issue.IssueTracking.TestedActual.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//24
if (issue.IssueTracking.DocumentedEstimate != null)
values.Add(new ExcelValueObject(issue.IssueTracking.DocumentedEstimate.Value.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//25
if (issue.IssueTracking.DocumentedActual != null)
values.Add(new ExcelValueObject(issue.IssueTracking.DocumentedActual.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
else
values.Add(new ExcelValueObject(String.Empty));
//26
if (totals.ImplementedTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.ImplementedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//27
if (totals.TestedTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.TestedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//28
if (totals.DocTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.DocPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//29 ACTIONS
if (totals.ImplementedTotal == 0)
values.Add(new ExcelValueObject(String.Empty));
else
values.Add(new ExcelValueObject(totals.ImplementedPercent.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Percent));
//30
values.Add(new ExcelValueObject(CmsWebService.GetUserName(issue.ModifiedById)));
//31
values.Add(new ExcelValueObject(issue.ModifiedDate.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
//end
excelXmlWriter.WriteRow(row++, values.ToArray());
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreateActionsSummaryWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Issue> issues)
{
excelXmlWriter.OpenWorkSheet("Actions - Summary");
//excelXmlWriter.WriteHeader(Styles);//does not work....
double[] colWidths = new[]
{
80d, 110d, 80d, 220d, 200d,
110d, 110d, 150d, 120d, 100d,
575d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"CMS Issue ID", "CMS Issue Title", "Action ID", "Action Description", "Action Initiated By",
"Action Initiated On", "Action Assigned To", "Action Required By", "Action Days Overdue","Action Implemented",
"Action Notes"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Issue issue in issues)
{
foreach (IssueRelatedAction action in issue.IssueRelatedActions)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1 CMS Issue ID
values.Add(new ExcelValueObject(issue.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2 CMS Issue Title
values.Add(new ExcelValueObject(issue.Name));
//3 Action ID
values.Add(new ExcelValueObject(action.Id.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//4 Action Desc
values.Add(new ExcelValueObject(action.Description));
//5 Action Initiated By
values.Add(new ExcelValueObject(action.InitiatedByUser.UserName));
//6 Action initiated on
if (action.InitiatedDate.HasValue)
{
values.Add(new ExcelValueObject(action.InitiatedDate.Value.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//7 Action Assigned To
if (action.AssignedToUser != null)
{
values.Add(new ExcelValueObject(action.AssignedToUser.UserName));
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//8 Required By
if (action.RequiredDate.HasValue)
{
values.Add(new ExcelValueObject(action.RequiredDate.Value.ToString("s"), ExcelCellFormat.DateTime, ExcelCellStyle.Date));
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//9 Days overdue
if (action.RequiredDate.HasValue)
{
TimeSpan ts = DateTime.Now - action.RequiredDate.Value;
if (ts.Days < 0)
{
values.Add(new ExcelValueObject(ts.Days.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
}
else
{
//RED BACKGROUND...cannot get to work using stupid styles...
values.Add(new ExcelValueObject(ts.Days.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
}
}
else
{
values.Add(new ExcelValueObject(String.Empty));
}
//10 implemented Yes/No
if (action.Implemented)
{
values.Add(new ExcelValueObject("Yes", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
else
{
values.Add(new ExcelValueObject("No", ExcelCellFormat.String, ExcelCellStyle.Normal));
}
//11 Notes
values.Add(new ExcelValueObject(action.Notes, ExcelCellFormat.String, ExcelCellStyle.Normal));
excelXmlWriter.WriteRow(row++, values.ToArray());
}
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreateWorksheet(ExcelXmlWriter excelXmlWriter, IEnumerable<Pipe> matchList, IList<Document> documents)
{
excelXmlWriter.OpenWorkSheet("Pipes");
double[] colWidths = new[]
{
30d, 150d, 150d, 150d, 150d,
75d, 100d, 100d, 75d
};
excelXmlWriter.OpenTable(200, colWidths.Length, colWidths, 1);
int row = 1;
string[] header = new[]
{
"Area", "Sequence No.", "Pipe Class", "Size (mm)", "Fluid Code", "Special Feature", "P&ID", "From", "To"
};
excelXmlWriter.WriteRow(row++, header, true);
foreach (Pipe pipe in matchList)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(pipe.Area.AreaNumber.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//2
values.Add(new ExcelValueObject(pipe.SequenceNo.ToString(), ExcelCellFormat.Number, ExcelCellStyle.Number));
//3
values.Add(new ExcelValueObject(pipe.PipeClass.Name));
//4
values.Add(new ExcelValueObject(pipe.PipeSize.Name));
//5
values.Add(new ExcelValueObject(pipe.PipeFluidCode.Name));
//6
values.Add(new ExcelValueObject(pipe.PipeSpecialFeature.Name));
//7
Document document = (from d in documents where d.Id == pipe.PIDDocumentId select d).FirstOrDefault();
if (document != null)
{
values.Add(new ExcelValueObject(document.Name));
}
else
{
values.Add(new ExcelValueObject(""));
}
//8
values.Add(new ExcelValueObject(pipe.From));
//9
values.Add(new ExcelValueObject(pipe.To));
//end
excelXmlWriter.WriteRow(row++, values.ToArray());
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
private void CreateWorksheet(string componentTypeName, ExcelXmlWriter excelXmlWriter, IEnumerable<PipeComponent> matchList, IList<PipeProperty> properties)
{
excelXmlWriter.OpenWorkSheet(componentTypeName);
List<string> header = new List<string>
{
"Pipe Name",
"Component Tag",
"Component Description"
};
List<double> colWidths = new List<double> { 80d, 150d, 150d };
//dynamic properties
header.AddRange(properties.Select(property => property.Name));
//dynamic cols - set widths
if (header.Count > colWidths.Count)
{
int k = header.Count - colWidths.Count;
for (int i = 0; i < k; i++)
{
colWidths.Add(80);
}
}
excelXmlWriter.OpenTable(200, colWidths.Count, colWidths.ToArray(), 1);
int row = 1;
excelXmlWriter.WriteRow(row++, header.ToArray(), true);
foreach (PipeComponent pipeComponent in matchList)
{
List<ExcelValueObject> values = new List<ExcelValueObject>();
//1
values.Add(new ExcelValueObject(pipeComponent.Pipe.Name));
//2
values.Add(new ExcelValueObject(pipeComponent.Name));
//3
values.Add(new ExcelValueObject(pipeComponent.Description));
//4
foreach (PipeProperty pipeComponentProperty in properties)
{
PipePropertyValue propertyValue = (from pv in pipeComponent.PipePropertyValues
where (pv.PipePropertyId == pipeComponentProperty.Id) && (pv.ComponentId==pipeComponent.Id)
select pv).FirstOrDefault();
if (propertyValue != null)
{
values.Add(new ExcelValueObject(propertyValue.Value, ExcelCellFormat.String));
}
else
{
values.Add(new ExcelValueObject(string.Empty, ExcelCellFormat.String));
}
}
//end
excelXmlWriter.WriteRow(row++, values.ToArray());
}
excelXmlWriter.CloseTable();
excelXmlWriter.CloseWorkSheet();
}
