/// <summary>
/// Helper method for initializing the databases for a whole division
/// </summary>
/// <param name="division"></param>
/// <param name="db"></param>
private static void InitializeDivision(Roster.Division division, SqliteConnection db)
{
String rosterCommand = "CREATE TABLE IF NOT " +
"EXISTS " + division + "DatabaseManager (ScupID INTEGER PRIMARY KEY, " +
"RegIndex INTEGER, Country TEXT, School TEXT, TeamID INTEGER, Position INTEGER, " +
"FirstName TEXT, LastName TEXT, Sex TEXT)";
String indScoresCommand = "CREATE TABLE IF NOT " +
"EXISTS " + division + "IndividualScores (ScupID INTEGER PRIMARY KEY, " +
"C_Arts INTEGER DEFAULT 0, C_Hist INTEGER DEFAULT 0, C_Lit INTEGER DEFAULT 0, C_Sci INTEGER DEFAULT 0, C_SStud INTEGER DEFAULT 0, C_SpecA INTEGER DEFAULT 0, " +
"Challenge INTEGER DEFAULT 0, Writing INTEGER DEFAULT 0, Debate INTEGER DEFAULT 0, Overall INTEGER DEFAULT 0)";
String teamScoresCommand = "CREATE TABLE IF NOT " +
"EXISTS " + division + "TeamScores (Team INTEGER PRIMARY KEY, " +
"Challenge INTEGER DEFAULT 0, Writing INTEGER DEFAULT 0, Debate INTEGER DEFAULT 0, Bowl INTEGER DEFAULT 0, Overall INTEGER DEFAULT 0)";
// TODO: do we initialize detailed debate/bowl/etc tables here as well? Probably?
SqliteCommand createRoster = new SqliteCommand(rosterCommand, db);
SqliteCommand createInd = new SqliteCommand(indScoresCommand, db);
SqliteCommand createTeam = new SqliteCommand(teamScoresCommand, db);
createRoster.ExecuteReader();
createInd.ExecuteReader();
createTeam.ExecuteReader();
}
/// <summary>
/// Make a new model for a scholar.
/// </summary>
/// <param name="id"></param>
/// <param name="index"></param>
/// <param name="country"></param>
/// <param name="school"></param>
/// <param name="teamID"></param>
/// <param name="pos"></param>
/// <param name="firstname"></param>
/// <param name="lastname"></param>
/// <param name="sex"></param>
/// <param name="division"></param>
public ScholarModel(int id, int index, string country, string school, int teamID, int pos, string firstname,
string lastname, string sex, Roster.Division division)
{
ScupID = id;
Index = index;
Country = country;
School = school;
TeamID = teamID;
Position = pos;
FirstName = firstname;
LastName = lastname;
Sex = sex;
Division = division;
}
public static async Task <ObservableCollection <ScholarModel> > ParseForScholars(StorageFile file, Roster.Division div)
{
var roster = new ObservableCollection <ScholarModel>();
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
IWorkbook workbook = await application.Workbooks.OpenAsync(file);
IWorksheet sheet = workbook.Worksheets[div.ToString()];
if (sheet == null)
{
throw new Exception("No worksheet named " + div + " found. Try again, buddy.");
}
int row = 2;
// basically keep going until there's no text in the row
while (sheet[row, 2].DisplayText.Length > 1)
{
int ScupID = (int)sheet[row, 4].Number * 10 + (int)sheet[row, 5].Number;
int Index = (int)sheet[row, 1].Number;
string Country = sheet[row, 2].Text;
string School = sheet[row, 3].Text;
int TeamID = (int)sheet[row, 4].Number;
int Position = (int)sheet[row, 5].Number;
string FirstName = sheet[row, 6].Text;
string LastName = sheet[row, 7].Text;
string Sex = sheet[row, 12].Text;
var newScholar = new ScholarModel(ScupID, Index, Country, School, TeamID, Position, FirstName, LastName,
Sex, div);
roster.Add(newScholar);
row++;
}
return(roster);
}
