/// <summary>
///gets name of family for spec
/// </summary>
/// <param name="spec">a spec object</param>
/// <returns>the family name</returns>
protected string GetFamilyNameForSpec(Context.Specs spec)
{
var ret = from p in dbContext.Controls
where p.Id == spec.ControId
select new { p.FamilyId };
uint id = ret.First().FamilyId;
var na = from p in dbContext.Families
where p.Id == id
select new { p.Name };
return(na.First().Name);
}
/// <summary>
/// return full spec name
/// </summary>
/// <param name="specid">id desired</param>
/// <returns>spec name</returns>
protected string GetSpecName(uint specid)
{
string name = "";
var ret =
from p in dbContext.Specs
where p.Id == specid
select p;
if (ret.Any())
{
Context.Specs sp = ret.First();
name +=
(from p in dbContext.Controls
where p.Id == sp.ControId
select p.Name).First();
name += sp.SpecificationlName;
}
return(name);
}
/// <summary>
/// organizes one control or spec
/// </summary>
/// <param name="row">row number</param>
/// <param name="rowData">place to put data</param>
/// <returns></returns>
private bool ProcessNextLine(int row, out string[] rowData)
{
//use none instead of blank
rowData = ReadExcelRow(row, 10);
if (isRowCompletelyEmpty(rowData))
{
return(true);
}
if (rowData[Constants.colDscription].StartsWith("[Withdrawn: "))
{
return(false);
}
if (rowData[Constants.colImpact] == "")
{
rowData[Constants.colImpact] = "NONE";
}
if (rowData[Constants.colPriority] == "")
{
rowData[Constants.colPriority] = "NONE";
}
string specsName = null;
string specsTail = null;
string specsPrefix = null;
Context.Controls newControl = null;
//replace - to ,
rowData[Constants.colImpact] = rowData[Constants.colImpact].Replace("_", ",");
// Process SPECS row
if (isRow4SpecsExtension(rowData))
{
specsName = GetCleanControlName(rowData[Constants.colNumber]);
specsTail = GetCleanTopControlName(rowData[Constants.colNumber]);
specsPrefix = specsName.Replace(specsTail, "");
// Get base Control for the current Specs
if (controlNames.Keys.Contains(specsPrefix))
{
/// Found a control in the list and the contol's name is in the specsPrefix variable
if (null == controlSpecs)
{
controlSpecs = new SortedList <string, SortedList <string, Context.Specs> >();
}
if (!controlSpecs.ContainsKey(specsPrefix))
{
controlSpecs.Add(specsPrefix, new SortedList <string, Context.Specs>());
}
Context.Specs newSpecs = new Context.Specs();
newSpecs.SpecificationlName = specsTail;
newSpecs.Description = rowData[Constants.colDscription];
newSpecs.Guidance = rowData[Constants.colGuidance];
controlSpecs[specsPrefix].Add(specsTail, newSpecs);
}
else if (!string.IsNullOrEmpty(specsTail))
{
string s = "";
}
else
{
throw (new Exception("Not specs for - " + specsName));
}
// Insert the details description
}
/// Process a CONTROL row
else if (isRow4Control(rowData))
{
// Stash up the collection of the related controls for the later
newControl = new Context.Controls();
newControl.BaselineID = FindBaselineId(rowData);
newControl.FamilyId = FindFamilyId(rowData);
newControl.PriorityId = FindPriorityId(rowData);
// Verify that Family, Baseline, Priority already exist or insert them
if (0 == newControl.BaselineID)
{
// Baseline was not found
throw(new Exception("Baseline for - " + rowData[Constants.colImpact] + " does not exist!!!"));
}
if (0 == newControl.FamilyId)
{
// Baseline was not found
throw (new Exception("Family with Name - " + GetControlsFamilyName(rowData) + " and Description - " + GetControlsFamilyDescription(rowData) + " does not exist!!!"));
}
if (0 == newControl.PriorityId)
{
// Baseline was not found
throw (new Exception("Priority for - " + rowData[Constants.colPriority] + " does not exist!!!"));
}
// TODO: Insert the Actual control with Family, Baseline, Priority verification (Future)
newControl.Name = GetCleanControlName(rowData[Constants.colNumber]);
newControl.Description = rowData[Constants.colDscription];
newControl.Guidance = rowData[Constants.colGuidance];
/// >>> dbContext.Controls.GetNewBindingList().Add(newControl)
if (!ControlExistsInDb(newControl))
{
ListNewControls.Add(newControl);
}
// Get the inserted control by name
if (!controlNames.ContainsKey(newControl.Name))
{
controlNames.Add(newControl.Name, newControl);
}
else
{
throw (new Exception("Control - " + newControl.Name + " happens more than once!!!"));
}
}
if (!string.IsNullOrEmpty(rowData[Constants.colRelated]))
{
if (null != newControl)
{
/// Parse out and add all Related Controls
///
string[] relateds = rowData[Constants.colRelated].Replace(" ", string.Empty).Split(new char[] { ',', ';' }, StringSplitOptions.RemoveEmptyEntries);
/// Add the related list to the collection (SAFELY)
if (null == controlRelated)
{
controlRelated = new SortedList <string, List <string> >();
}
if (!controlRelated.ContainsKey(newControl.Name))
{
controlRelated.Add(newControl.Name, new List <string>(relateds));
}
}
}
return(false);
}
/// <summary>
/// Actual work
/// </summary>
public void CreateReport(string excelReport2Create, List <string> input, BackgroundWorker bw)
{
try
{
if (InitNewExcelFile(excelReport2Create))
{
InitDataModel();
var families = (from fams in dbContext.Families
select fams).ToList <Context.Families>().ToLookup(x => x.Id, x => x);
var controls = (from ctrl in dbContext.Controls
select ctrl).ToList <Context.Controls>();
int row = 1, col = 1;
int numcontrols = input.Count;
double total = 0, inc = 100.0 / numcontrols;
palette = GetPalette();
//format header
this.activeWorksheet.SetFont(18, 1, 1, 2, 20);
this.activeWorksheet.wrapText(1, 1, 2, 45);
this.activeWorksheet.Center(1, 1, 2, 45);
this.activeWorksheet.SetFont(14, 1, 1, 2, 45);
//column widths
this.activeWorksheet.ColumnWidth(col++, 25);
this.activeWorksheet.ColumnWidth(col++, 15);
this.activeWorksheet.ColumnWidth(col++, 16.43);
this.activeWorksheet.ColumnWidth(col++, 26.71);
this.activeWorksheet.ColumnWidth(col++, 28.29);
this.activeWorksheet.ColumnWidth(col++, 21.71);
this.activeWorksheet.ColumnWidth(col++, 28.29);
this.activeWorksheet.ColumnWidth(col++, 10.47);
this.activeWorksheet.ColumnWidth(col++, 30);
col += 7;
this.activeWorksheet.ColumnWidth(col, 30);
int black = ColorExtensions.TranslateToExcelColor(System.Drawing.Color.FromArgb(255, 255, 255));
int fg = ColorExtensions.TranslateToExcelColor(System.Drawing.Color.FromArgb(0, 0, 0));
int bg = ColorExtensions.TranslateToExcelColor(System.Drawing.Color.FromArgb(217, 217, 217));
//header
col = 10;
//this.activeWorksheet.setMergedCellTo(row,col,"Capability Structure", 5, bg, fg);
//col += 7;
this.activeWorksheet.setMergedCellTo(row, col, "Capability Implementation\nSP800-53 Rev4", 3, bg, fg);
col += 4;
this.activeWorksheet.setMergedCellTo(row, col, "Information Protection\nP800-53 Rev4", 3, bg, fg);
row++;
col = 1;
this.activeWorksheet.setCellTo(row, col++, "Control Family", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "Control", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "Domain", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "Container", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "Capability (proccess or solution)", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "Capability (proccess or solution)", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "Unique ID", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "Scope", bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, "TIC Capabilities Mapping", bg, fg, true);
int start = col;
// Scope and Impact Levels
this.activeWorksheet.setCellTo(row, col++, "Low", palette[0][0], palette[0][1], true);
this.activeWorksheet.setCellTo(row, col++, "Mod.", palette[1][0], palette[1][1], true);
this.activeWorksheet.setCellTo(row, col++, "High", palette[2][0], palette[2][1], true);
// PM Control
this.activeWorksheet.setHeaderCellTo(row, col++, "PM");
// Scope and Impact Levels
this.activeWorksheet.setCellTo(row, col++, "Low", palette[0][0], palette[0][1], true);
this.activeWorksheet.setCellTo(row, col++, "Mod.", palette[1][0], palette[1][1], true);
this.activeWorksheet.setCellTo(row, col++, "High", palette[2][0], palette[2][1], true);
// Relateds
this.activeWorksheet.setCellTo(row, col++, "Related Controls", bg, fg, true);
this.activeWorksheet.Hide(7);
this.activeWorksheet.Border(2, 1, 2, 9);
this.activeWorksheet.SetHeight(1, 57);
this.activeWorksheet.SetHeight(2, 55);
familyPalette = GetFamilyPalette();
domainPalette = GetDomainPalette();
foreach (string nam in input)
{
col = 1;
row++;
//print control info
if (isRow4Control(nam))
{
uint id = GetControlIdByName(nam);
var x = from p in dbContext.Controls
where p.Id == id
select p;
Context.Controls control = x.First();
bg = familyPalette[families[control.FamilyId].First().Name.Trim()][0];
fg = familyPalette[families[control.FamilyId].First().Name.Trim()][1];
this.activeWorksheet.setCellTo(row, col++, families[control.FamilyId].First().Description.Trim(), bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, control.Name, bg, fg, true);
List <Context.Capabilities> theCaps = this.GetCapabilitiesForControlId(control.Id);
this.activeWorksheet.setCellTo(row, 17, GetRelatedControlsString(control.Name));
if (theCaps.Count == 0)
{
total += inc;
bw.ReportProgress((int)total);
}
else
{
double capinc = inc / theCaps.Count;
PrintCaps(theCaps, ref row, ref col, control.Id, true, bw, capinc, ref total);
}
}
else
{
//spec info
Context.Specs spec = GetSpecByName(nam);
bg = familyPalette[GetFamilyNameForSpec(spec)][0];
fg = familyPalette[GetFamilyNameForSpec(spec)][1];
this.activeWorksheet.setCellTo(row, col++, GetFamilyForSpec(spec.Id), bg, fg, true);
this.activeWorksheet.setCellTo(row, col++, GetSpecName(spec.Id), bg, fg, true);
List <Context.Capabilities> caps = GetCapabilitiesForSpecId(spec.Id);
if (caps.Count == 0)
{
total += inc;
bw.ReportProgress((int)total);
}
else
{
double capinc = inc / caps.Count;
PrintCaps(caps, ref row, ref col, spec.Id, false, bw, capinc, ref total);
}
}
row++;
}
bw.ReportProgress(100);
this.activeWorksheet.Border(1, start, row - 2, start + 6);
this.activeWorksheet.fit(3, 1, row - 1, 17);
this.activeWorksheet.SetFont(12, 3, 1, row - 1, 17);
}
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{
CloseSaveExcel();
}
}
