Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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
        }