/// <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
}