public async Task <IActionResult> GetCompliancBySystem(string id, string filter, bool pii, string majorcontrol = "")
 {
     if (!string.IsNullOrEmpty(id))
     {
         try {
             _logger.LogInformation("Calling GetCompliancBySystem({0}, {1}, {2})", id, filter, pii.ToString());
             var result = ComplianceGenerator.GetSystemControls(id, filter, pii, majorcontrol);
             if (result != null && result.Result != null && result.Result.Count > 0)
             {
                 _logger.LogInformation("Called GetCompliancBySystem({0}, {1}, {2}) successfully", id, filter, pii.ToString());
                 return(Ok(result));
             }
             else
             {
                 _logger.LogWarning("Called GetCompliancBySystem({0}, {1}, {2}) but had no returned data", id, filter, pii.ToString());
                 return(NotFound()); // bad system reference
             }
         }
         catch (Exception ex) {
             _logger.LogError(ex, "GetCompliancBySystem() Error listing all checklists for system {0}", id);
             return(BadRequest());
         }
     }
     else
     {
         _logger.LogWarning("Called GetCompliancBySystem() but with an invalid or empty Id", id);
         return(BadRequest()); // no term entered
     }
 }
        public async Task <IActionResult> GetCompliancBySystemExport(string id, string filter, bool pii, string majorcontrol = "")
        {
            if (!string.IsNullOrEmpty(id))
            {
                try {
                    _logger.LogInformation("Calling GetCompliancBySystemExport({0}, {1}, {2})", id, filter, pii.ToString());
                    // verify system information
                    SystemGroup sg = NATSClient.GetSystemGroup(id);
                    if (sg == null)
                    {
                        _logger.LogInformation("Called GetCompliancBySystemExport({0}, {1}, {2}) invalid System Group", id, filter, pii.ToString());
                        return(NotFound());
                    }

                    var result = ComplianceGenerator.GetSystemControls(id, filter, pii, majorcontrol);
                    if (result != null && result.Result != null && result.Result.Count > 0)
                    {
                        _logger.LogInformation("Called GetCompliancBySystemExport({0}, {1}, {2}) successfully. Putting into XLSX.", id, filter, pii.ToString());

                        // starting row
                        uint rowNumber = 7;
                        // create the XLSX in memory and send it out
                        var memory = new MemoryStream();
                        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(memory, SpreadsheetDocumentType.Workbook))
                        {
                            // Add a WorkbookPart to the document.
                            WorkbookPart workbookpart = spreadSheet.AddWorkbookPart();
                            workbookpart.Workbook = new Workbook();

                            // add styles to workbook
                            WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();

                            // Add a WorksheetPart to the WorkbookPart.
                            WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
                            worksheetPart.Worksheet = new Worksheet(new SheetData());

                            // add stylesheet to use cell formats 1 - 4
                            wbsp.Stylesheet = ExcelStyleSheet.GenerateStylesheet();

                            DocumentFormat.OpenXml.Spreadsheet.Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Columns>();
                            if (lstColumns == null)   // generate the column listings we need with custom widths
                            {
                                lstColumns = new DocumentFormat.OpenXml.Spreadsheet.Columns();
                                lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
                                {
                                    Min = 1, Max = 1, Width = 20, CustomWidth = true
                                });                                                                                                                      // col A
                                lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
                                {
                                    Min = 2, Max = 2, Width = 60, CustomWidth = true
                                });
                                lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
                                {
                                    Min = 3, Max = 3, Width = 50, CustomWidth = true
                                });
                                lstColumns.Append(new DocumentFormat.OpenXml.Spreadsheet.Column()
                                {
                                    Min = 4, Max = 4, Width = 25, CustomWidth = true
                                });
                                worksheetPart.Worksheet.InsertAt(lstColumns, 0);
                            }

                            // Add Sheets to the Workbook.
                            Sheets sheets = spreadSheet.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());

                            // Append a new worksheet and associate it with the workbook.
                            Sheet sheet = new Sheet()
                            {
                                Id = spreadSheet.WorkbookPart.
                                     GetIdOfPart(worksheetPart), SheetId = 1, Name = "System-Compliance"
                            };
                            sheets.Append(sheet);
                            // Get the sheetData cell table.
                            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
                            DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
                            DocumentFormat.OpenXml.Spreadsheet.Cell newCell = null;

                            DocumentFormat.OpenXml.Spreadsheet.Row row = MakeTitleRow("OpenRMF by Cingulara and Tutela");
                            sheetData.Append(row);
                            row = MakeXLSXInfoRow("System Name", sg.title, 2);
                            sheetData.Append(row);
                            row = MakeXLSXInfoRow("Generated", DateTime.Now.ToString("MM/dd/yy hh:mm tt"), 7);
                            sheetData.Append(row);
                            row = MakeComplianceHeaderRows(rowNumber);
                            sheetData.Append(row);

                            uint styleIndex = 0; // use this for 4, 5, 6, or 7 for status

                            _logger.LogInformation("GetCompliancBySystemExport() cycling through all the vulnerabilities");

                            foreach (NISTCompliance nist in result.Result)
                            {
                                if (nist.complianceRecords.Count > 0)
                                {
                                    foreach (ComplianceRecord rec in nist.complianceRecords)
                                    {
                                        rowNumber++;
                                        styleIndex = GetVulnerabilityStatus(rec.status, "high");
                                        // make a new row for this set of items
                                        row = MakeDataRow(rowNumber, "A", nist.control, styleIndex);
                                        // now cycle through the rest of the items
                                        newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
                                        {
                                            CellReference = "B" + rowNumber.ToString()
                                        };
                                        row.InsertBefore(newCell, refCell);
                                        newCell.CellValue  = new CellValue(nist.title);
                                        newCell.DataType   = new EnumValue <CellValues>(CellValues.String);
                                        newCell.StyleIndex = styleIndex;
                                        newCell            = new DocumentFormat.OpenXml.Spreadsheet.Cell()
                                        {
                                            CellReference = "C" + rowNumber.ToString()
                                        };
                                        row.InsertBefore(newCell, refCell);
                                        newCell.CellValue  = new CellValue(rec.title);
                                        newCell.DataType   = new EnumValue <CellValues>(CellValues.String);
                                        newCell.StyleIndex = styleIndex;
                                        newCell            = new DocumentFormat.OpenXml.Spreadsheet.Cell()
                                        {
                                            CellReference = "D" + rowNumber.ToString()
                                        };
                                        row.InsertBefore(newCell, refCell);
                                        // print out status, if N/A or NAF then just NAF
                                        if (rec.status.ToLower() == "open")
                                        {
                                            newCell.CellValue = new CellValue("Open");
                                        }
                                        else if (rec.status.ToLower() == "not_reviewed")
                                        {
                                            newCell.CellValue = new CellValue("Not Reviewed");
                                        }
                                        else
                                        {
                                            newCell.CellValue = new CellValue("Not a Finding");
                                        }
                                        newCell.DataType   = new EnumValue <CellValues>(CellValues.String);
                                        newCell.StyleIndex = styleIndex;
                                        sheetData.Append(row);
                                    }
                                }
                                else
                                {
                                    rowNumber++;
                                    styleIndex = 0;
                                    // make a new row for this set of items
                                    row = MakeDataRow(rowNumber, "A", nist.control, styleIndex);
                                    // now cycle through the rest of the items
                                    newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
                                    {
                                        CellReference = "B" + rowNumber.ToString()
                                    };
                                    row.InsertBefore(newCell, refCell);
                                    newCell.CellValue  = new CellValue(nist.title);
                                    newCell.DataType   = new EnumValue <CellValues>(CellValues.String);
                                    newCell.StyleIndex = styleIndex;
                                    sheetData.Append(row);
                                }
                            }

                            // Save the new worksheet.
                            workbookpart.Workbook.Save();
                            // Close the document.
                            spreadSheet.Close();
                            // set the filename
                            string filename = sg.title;
                            if (!string.IsNullOrEmpty(sg.title) && sg.title.ToLower().Trim() == "none")
                            {
                                filename = sg.title.Trim() + "-" + filename; // add the system onto the front
                            }
                            // return the file
                            memory.Seek(0, SeekOrigin.Begin);
                            _logger.LogInformation("Called GetCompliancBySystemExport({0}, {1}, {2}) successfully", id, filter, pii.ToString());
                            return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", CreateXLSXFilename(filename)));
                        } // end of using statement
                    }
                    else
                    {
                        _logger.LogWarning("Called GetCompliancBySystemExport({0}, {1}, {2}) but had no returned data", id, filter, pii.ToString());
                        return(NotFound()); // bad system reference
                    }
                }
                catch (Exception ex) {
                    _logger.LogError(ex, "GetCompliancBySystemExport() Error exporting Compliance for system {0}", id);
                    return(BadRequest());
                }
            }
            else
            {
                _logger.LogWarning("Called GetCompliancBySystemExport() but with an invalid or empty system group Id", id);
                return(BadRequest()); // no term entered
            }
        }