/// <summary> /// Creates "NA" answers for any questions that were filtered out by /// the AWWA Tool, and were not included in the spreadsheet. /// </summary> /// <returns></returns> private List <AwwaControlAnswer> FilteredOutQuestions(List <string> submittedControlIDs, DBIO dbio) { var sqlAwwaFullControlList = "select requirement_title from NEW_REQUIREMENT where original_set_name = 'AWWA'"; var dtfullList = dbio.Select(sqlAwwaFullControlList, null); var listNaAnswers = new List <AwwaControlAnswer>(); foreach (DataRow r in dtfullList.Rows) { var c = r["REQUIREMENT_TITLE"].ToString(); if (!submittedControlIDs.Contains(c)) { // build a NA var answer = new AwwaControlAnswer() { Answer = "", ControlID = c, CsetAnswer = "NA", CsetComment = "[Screened out by the AWWA Cybersecurity Tool]" }; listNaAnswers.Add(answer); } } return(listNaAnswers); }
/// <summary> /// /// </summary> /// <param name="zipFileFromDatabase"></param> /// <param name="currentUserId"></param> /// <returns></returns> public async Task <String> ProcessSpreadsheetImport(byte[] spreadsheet, int assessmentId) { var stream = new MemoryStream(spreadsheet); using (SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, false)) { // get API page content as a DataTable and pull reference values from it // Not sure how to use this number to find the right worksheet using OpenXML... // var targetSheetIndex = int.Parse(GetCellValue(doc, "API", "B2")) - 1; // ... so for now, using sheet name ... AwwaSheetConfig config = new AwwaSheetConfig(doc); var targetSheetPart = config.GetWorksheetPartByName(doc, config.targetSheetName); if (targetSheetPart == null || !config.sheetIsValid) { return("Failed to find the correct sheet within the document"); //return false, signifying the import process failed } var answerMap = config.GetAnswerMap(); //find target sheet number of rows IEnumerable <SheetData> sheetData = targetSheetPart.Worksheet.Elements <SheetData>(); int maxRow = 0; foreach (SheetData sd in sheetData) { IEnumerable <Row> row = sd.Elements <Row>(); // Get the row IEnumerator maxRow = row.Count(); } DBIO dbio = new DBIO(); // track what is submitted so that we can build NA answers for what's missing List <string> submittedControlIDs = new List <string>(); List <AwwaControlAnswer> mappedAnswers = new List <AwwaControlAnswer>(); for (var i = config.targetSheetStartRow; i < maxRow; i++) { var controltmpId = GetCellValue(doc, config.targetSheetName, string.Format("{0}{1}", config.cidColRef, i)); var controlID = config.GetControlId(controltmpId); if (string.IsNullOrWhiteSpace(controlID)) { break; } submittedControlIDs.Add(controlID); var awwaAnswer = GetCellValue(doc, config.targetSheetName, string.Format("{0}{1}", config.statusColRef, i)); if (awwaAnswer != null) { awwaAnswer = awwaAnswer.Trim().ToLower(); var mappedAnswer = answerMap.Where(x => x.AwwaAnswer?.ToLower() == awwaAnswer).FirstOrDefault(); if (mappedAnswer != null) { var a = new AwwaControlAnswer() { ControlID = controlID, Answer = awwaAnswer, CsetAnswer = mappedAnswer.CsetAnswer, CsetComment = mappedAnswer.CsetComment }; mappedAnswers.Add(a); } } } // include NA answers for any AWWA questions that were not in the spreadsheet mappedAnswers.AddRange(FilteredOutQuestions(submittedControlIDs, dbio)); // at this point, CSET assessment answers can be built from the 'mappedAnswers' collection ... string sql = "select r.Requirement_Title, r.Requirement_Id, q.Question_Id " + "from new_requirement r " + "left join requirement_questions rq on r.Requirement_Id = rq.Requirement_Id " + "left join new_question q on rq.Question_Id = q.Question_Id " + "where r.Original_Set_Name = 'AWWA' " + "order by r.Requirement_Title"; DataTable dt = dbio.Select(sql, null); var sqlInsert = "insert into ANSWER (Assessment_Id, Is_Requirement, Question_Or_Requirement_Id, Mark_For_Review, Comment, Alternate_Justification, Question_Number, Answer_Text, Component_Guid, Is_Component, Custom_Question_Guid, Is_Framework, Is_Maturity, Old_Answer_Id, Reviewed, FeedBack) " + "values (@assessid, @isreq, @questionreqid, 0, @comment, '', @questionnum, @ans, '00000000-0000-0000-0000-000000000000', 0, null, 0, 0, null, 0, null)"; var sqlUpdate = "update ANSWER set Answer_Text = @ans, Comment = @comment where Assessment_Id = @assessid and Question_Or_Requirement_Id = @questionreqid and Is_Requirement = @isreq"; QuestionsManager qm = new QuestionsManager(assessmentId); foreach (var a in mappedAnswers) { // figure out the question ID that corresponds to the AWWA Control ID ... var g = dt.Select(string.Format("requirement_title = '{0}'", a.ControlID)); if (g.Length == 0) { continue; } DataRow mappedQuestionAndRequirement = g[0]; // Insert or update a Requirement answer try { var parmsReq = new Dictionary <string, object>() { { "@assessid", assessmentId }, { "@isreq", 1 }, { "@questionreqid", mappedQuestionAndRequirement["requirement_id"] }, { "@comment", a.CsetComment }, { "@questionnum", 0 }, { "@ans", a.CsetAnswer } }; dbio.Execute(sqlInsert, parmsReq); } catch (Exception exc) { // get any existing comment and append to it; don't overlay it var comment = GetExistingComment(assessmentId, (int)mappedQuestionAndRequirement["requirement_id"]); if (a.CsetComment.Trim() != comment.Trim()) { comment = comment + (comment.Trim().Length > 0 && a.CsetComment.Trim().Length > 0 ? " - " : "") + a.CsetComment; } else { comment = a.CsetComment; } var parmsReq = new Dictionary <string, object>() { { "@ans", a.CsetAnswer }, { "@comment", comment }, { "@assessid", assessmentId }, { "@questionreqid", mappedQuestionAndRequirement["requirement_id"] }, { "@isreq", 1 } }; dbio.Execute(sqlUpdate, parmsReq); } // Insert or update a Question answer try { var parmsQ = new Dictionary <string, object>() { { "@assessid", assessmentId }, { "@isreq", 0 }, { "@questionreqid", mappedQuestionAndRequirement["question_id"] }, { "@comment", a.CsetComment }, { "@questionnum", 0 }, { "@ans", a.CsetAnswer } }; dbio.Execute(sqlInsert, parmsQ); } catch (Exception exc) { // get any existing comment and append to it; don't overlay it var comment = GetExistingComment(assessmentId, (int)mappedQuestionAndRequirement["question_id"]); if (a.CsetComment.Trim() != comment.Trim()) { comment = comment + (comment.Trim().Length > 0 && a.CsetComment.Trim().Length > 0 ? " - " : "") + a.CsetComment; } else { comment = a.CsetComment; } var parmsQ = new Dictionary <string, object>() { { "@ans", a.CsetAnswer }, { "@comment", comment }, { "@assessid", assessmentId }, { "@questionreqid", mappedQuestionAndRequirement["question_id"] }, { "@isreq", 0 } }; dbio.Execute(sqlUpdate, parmsQ); } } } return(null); //return empty, signiying the import process completed }