/// <summary>
/// Does a quick check on the validity of the spreasheet.
/// </summary>
/// <param name="teamInfoSpreadsheet"></param>
/// <param name="cell1">Value of cell at [0,0]</param>
/// <param name="minRows">Minimum number of rows</param>
/// <param name="minCols">Minimum number of columns</param>
/// <returns></returns>
private static bool quickSpreadsheetCheck(SimpleSpreadsheetReader teamInfoSpreadsheet, string cell1, int minRows, int minCols)
{
minRows = Math.Max(minRows, 2);
minCols = Math.Max(minCols, 1);
if (teamInfoSpreadsheet.getNumRows() < minRows || teamInfoSpreadsheet.getNumCols() < minCols)
{
Trace.WriteLine("Spreadsheet dimensions too small.");
return(false);
}
String[] cells = teamInfoSpreadsheet.getRowCells(0, 0, 0);
String c1 = stripEndBlanks(cells[0]);
if (!c1.Equals(cell1))
{
Trace.WriteLine("Cell 1 (signature) is incorrect.");
return(false);
}
return(true);
}
/// <summary>
/// Get's the team info (id, name) associated with the machine, if found. Returns null otherwise.
/// Throws ArgumentException if there is an issue with the spreadsheet data.
/// </summary>
/// <param name="teamInfoSpreadsheet"></param>
/// <returns></returns>
public static TeamInfo getTeamInfoForMachine(SimpleSpreadsheetReader teamInfoSpreadsheet)
{
String machineName = normalizeMachineName(Environment.MachineName);
TeamInfo info = null;
// We expect the order of info in the spreadsheet to be:
// TeamID, TeamName, MachineName, ...
// Also, the 1st row is expected to be properties and the 2nd row to be the header.
if (!quickSpreadsheetCheck(teamInfoSpreadsheet, "PTD", 2, 3))
{
String message = "Does not appear to be a valid team info spreadsheet - signature missing or invalid";
ErrorReport.logError(message);
throw new ArgumentException(message);
}
// Start at 1 to skip header row.
for (int r = 1; r < teamInfoSpreadsheet.getNumRows(); r++)
{
String[] row = teamInfoSpreadsheet.getRowCells(r, 0, 2);
String machineName_r = normalizeMachineName(row[2]);
if (machineName.Equals(machineName_r))
{
// Found it!
String teamId = stripEndBlanks(row[0]);
String teamName = stripEndBlanks(row[1]);
// We expect team ID to be T followed by a digit sequence.
if (!Regex.IsMatch(teamId, "^T[0-9]+$"))
{
String message = String.Format("Team Info: matched Team ID [{0}] doesn't appear to be a valid team ID (row={1})", teamId, r + 1);
ErrorReport.logError(message);
throw new ArgumentException(message);
}
info = new TeamInfo(teamId, teamName);
break;
}
}
return(info);
}
/// <summary>
/// Adds all puzzles in the specified spreadsheet. Individual puzzles may not
/// be added for a variety of reasons including:
/// - Duplicate Puzzle ID (only first one gets added)
/// - Empty answer field.
/// - Invalid REGEX field in answer or hints.
/// Errors are reported as Trace output.
/// TODO: Have a systematic way to report skipped puzzles.
/// </summary>
/// <param name="sr"></param>
private void addPuzzles(SimpleSpreadsheetReader sr)
{
// We expect that the first row contains the signature ("POD") followed by "version:1.0" followed by
// additional properties (which we ignore for now)
const int HEADER_ROWS = 2;
const int MIN_COLS = 3;
const String FILE_SIGNATURE = "POD";
const String PROP_ENCRYPTED = "encrypted";
int sheet = 0;
bool encrypted = false; // whether answers and hints are encrypted or not.
int numRows = sr.getNumRows(sheet);
int numCols = sr.getNumCols(sheet);
if (numRows < HEADER_ROWS || numCols < MIN_COLS)
{
ErrorReport.logError("Puzzle data spreadsheet is too small!");
throw new ArgumentException();
}
String[] propertyRow = sr.getRowCells(0, 0, numCols - 1, sheet);
String[] header = sr.getRowCells(1, 0, numCols - 1, sheet);
// We expect the first property cell to be POD (all caps)
if (!Utils.stripEndBlanks(propertyRow[0]).Equals(FILE_SIGNATURE))
{
ErrorReport.logError("Puzzle data spreadsheet has an invalid/missing signature.");
throw new ArgumentException();
}
// Read rest of properties
readProperties(propertyRow);
// Check if answer keys are encrypted.
if (properties.ContainsKey(PROP_ENCRYPTED))
{
encrypted = true;
}
this.sourceIsEncrypted = encrypted;
// We expect the first header cell to be "id"
String headerId = Utils.stripEndBlanks(header[0]);
if (!headerId.Equals(SPREADSHEET_LABEL_ID, StringComparison.CurrentCultureIgnoreCase))
{
ErrorReport.logError("Puzzle data spreadsheet does not have the ID field.");
throw new ArgumentException();
}
int startRow = HEADER_ROWS; // First row of puzzle data
int startCol = 0; // First col of puzzle data
//int puzzleCount = numRows - HEADER_ROWS; // could be zero; it's valid to have 0 puzzles.
for (int i = startRow; i < numRows; i++)
{
String[] sRow = sr.getRowCells(i, startCol, numCols - 1, sheet);
const String REGEX_ID = @"^[0-9][0-9][0-9]$"; // For now, IDs must be 3-digit numbers.
String id = Utils.stripBlanks(sRow[0]);
if (!Regex.IsMatch(id, REGEX_ID))
{
Trace.WriteLine(String.Format("Skipping row {0}: invalid ID", i));
continue;
}
// We got the ID, if needed decrypt remaining fields after Name
if (encrypted)
{
decryptCells(id, sRow, 2, numCols - 1); // 2 == skip Id and Name. False == descrypt
}
// Now let's get the remaining columns. First, get the first two: Name and Answer.
String name = Utils.stripEndBlanks(sRow[1]);
String answer = Utils.stripEndBlanks(sRow[2]);
// Neither should be blank.
if (name.Equals("") || answer.Equals(""))
{
Trace.WriteLine(String.Format("Skipping row {0}: blank Name or Answer", i));
continue;
}
PuzzleResponse pAnswerResponse = buildPuzzleResponse(answer, PuzzleResponse.ResponseType.Correct);
if (pAnswerResponse == null)
{
Trace.WriteLine(String.Format("Skipping row {0}: Invalid Answer", i));
continue;
}
PuzzleInfo pi = new PuzzleInfo(id, name);
pi.addResponse(pAnswerResponse);
// Add hints, if any...
for (int j = 3; j < sRow.Length; j++)
{
String field = Utils.stripEndBlanks(sRow[j]);
if (field.Length > 0)
{
PuzzleResponse pr = buildPuzzleResponse(field, PuzzleResponse.ResponseType.Incorrect);
if (pr == null)
{
Trace.WriteLine(String.Format("Ignoring hint {0} on row {1}: Invalid hint content", j, i));
continue;
}
pi.addResponse(pr);
}
}
try
{
puzzles.Add(id, pi);
puzzleIDs.Add(id);
Trace.WriteLine(String.Format("Adding row {0}: Puzzle ID {1}, Answer {2}", i, id, answer));
}
catch (ArgumentException)
{
Trace.WriteLine(String.Format("Ignoring row {0}: Duplicate ID {1}", i, id));
}
}
}