//Query that returns selected Race
public IActionResult Race(string id)
{
string cs = "Filename =./SD.db";
SqliteConnection conn = new SqliteConnection(cs);
SqliteCommand cmd;
List <LookupRace> groupExcluded = new List <LookupRace>();
List <LookupRace> groupProceed = new List <LookupRace>();
List <LookupRace> groupQualified = new List <LookupRace>();
List <LookupRace> groupNA = new List <LookupRace>();
List <LookupOutcome> groupRace = new List <LookupOutcome>();
string race = id;
ViewBag.MyString = id;
string Race = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE RACE='" + race + "' GROUP BY YOS3_OUT";
string Excluded = "SELECT RACE, COUNT(*) FROM GENERAL WHERE YOS3_OUT = 'Excluded' GROUP BY RACE";
string Proceed = "SELECT RACE, COUNT(*) FROM GENERAL WHERE YOS3_OUT = 'Proceed' GROUP BY RACE";
string Qualified = "SELECT RACE, COUNT(*) FROM GENERAL WHERE YOS3_OUT = 'Qualified' GROUP BY RACE";
string NA = "SELECT RACE, COUNT(*) FROM GENERAL WHERE YOS3_OUT = 'Not Categorised' GROUP BY RACE";
conn.Open();
if ((conn.State & System.Data.ConnectionState.Open) > 0)
{
cmd = new SqliteCommand(Race, conn);
SqliteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupRace.Add(obj);
}
cmd = new SqliteCommand(Excluded, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupRace obj = new LookupRace();
obj.race = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupExcluded.Add(obj);
}
cmd = new SqliteCommand(Proceed, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupRace obj = new LookupRace();
obj.race = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupQualified.Add(obj);
}
cmd = new SqliteCommand(Qualified, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupRace obj = new LookupRace();
obj.race = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupProceed.Add(obj);
}
cmd = new SqliteCommand(NA, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupRace obj = new LookupRace();
obj.race = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupNA.Add(obj);
}
}
conn.Close();
ViewBag.Race = groupRace;
ViewBag.Excluded = groupExcluded;
ViewBag.Proceed = groupProceed;
ViewBag.Qualified = groupQualified;
ViewBag.NA = groupNA;
return(View());
}
// GET: /<controller>/
public IActionResult Landing()
{
//Verifies that user has successfully logged in before proceeding
if (HttpContext.Session.GetString("UserId") != null)
{
ViewBag.Username = HttpContext.Session.GetString("Username");
//Connection to Database
string cs = "Filename =./SD.db";
SqliteConnection conn = new SqliteConnection(cs);
SqliteCommand cmd;
//Create lists to store data from each query
List <LookupOutcome> group2008 = new List <LookupOutcome>();
List <LookupOutcome> group2009 = new List <LookupOutcome>();
List <LookupOutcome> group2010 = new List <LookupOutcome>();
List <LookupOutcome> group2011 = new List <LookupOutcome>();
List <LookupOutcome> group2012 = new List <LookupOutcome>();
List <LookupOutcome> group2013 = new List <LookupOutcome>();
List <LookupOutcome> group2014 = new List <LookupOutcome>();
List <LookupOutcome> group2015 = new List <LookupOutcome>();
List <LookupOutcome> group2016 = new List <LookupOutcome>();
List <LookupOutcome> group2017 = new List <LookupOutcome>();
List <LookupOutcome> group2018 = new List <LookupOutcome>();
//SQL Query Strings
string Outcome2008 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2008' GROUP BY YOS3_OUT";
string Outcome2009 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2009' GROUP BY YOS3_OUT";
string Outcome2010 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2010' GROUP BY YOS3_OUT";
string Outcome2011 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2011' GROUP BY YOS3_OUT";
string Outcome2012 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2012' GROUP BY YOS3_OUT";
string Outcome2013 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2013' GROUP BY YOS3_OUT";
string Outcome2014 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2014' GROUP BY YOS3_OUT";
string Outcome2015 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2015' GROUP BY YOS3_OUT";
string Outcome2016 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2016' GROUP BY YOS3_OUT";
string Outcome2017 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2017' GROUP BY YOS3_OUT";
string Outcome2018 = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE REG_END = '2018' GROUP BY YOS3_OUT";
conn.Open();
if ((conn.State & System.Data.ConnectionState.Open) > 0)
{
//Execution of query
cmd = new SqliteCommand(Outcome2008, conn);
SqliteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//Results from query added to List
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2008.Add(obj);
}
cmd = new SqliteCommand(Outcome2009, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2009.Add(obj);
}
cmd = new SqliteCommand(Outcome2010, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2010.Add(obj);
}
cmd = new SqliteCommand(Outcome2011, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2011.Add(obj);
}
cmd = new SqliteCommand(Outcome2012, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2012.Add(obj);
}
cmd = new SqliteCommand(Outcome2013, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2013.Add(obj);
}
cmd = new SqliteCommand(Outcome2014, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2014.Add(obj);
}
cmd = new SqliteCommand(Outcome2015, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2015.Add(obj);
}
cmd = new SqliteCommand(Outcome2016, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2016.Add(obj);
}
cmd = new SqliteCommand(Outcome2017, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2017.Add(obj);
}
cmd = new SqliteCommand(Outcome2018, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
group2018.Add(obj);
}
}
conn.Close();
//Data from lists added to ViewBag to be passed to View
ViewBag.year08 = group2008;
ViewBag.year09 = group2009;
ViewBag.year10 = group2010;
ViewBag.year11 = group2011;
ViewBag.year12 = group2012;
ViewBag.year13 = group2013;
ViewBag.year14 = group2014;
ViewBag.year15 = group2015;
ViewBag.year16 = group2016;
ViewBag.year17 = group2017;
ViewBag.year18 = group2018;
return(View());
}
else
{
//If user not successfully logged in
return(RedirectToAction("Login"));
}
}
//Query that returns selected Province
public IActionResult Province(string id)
{
string cs = "Filename =./SD.db";
SqliteConnection conn = new SqliteConnection(cs);
SqliteCommand cmd;
List <LookupOutcome> groupMale = new List <LookupOutcome>();
List <LookupOutcome> groupFemale = new List <LookupOutcome>();
List <LookupProv> groupExcluded = new List <LookupProv>();
List <LookupProv> groupQualified = new List <LookupProv>();
List <LookupProv> groupProceed = new List <LookupProv>();
List <LookupProv> groupNotCategorised = new List <LookupProv>();
string prov = id;
if (id == "EC")
{
ViewBag.MyString = "Eastern Cape";
}
else if (id == "FS")
{
ViewBag.MyString = "Free State";
}
else if (id == "GA")
{
ViewBag.MyString = "Gauteng";
}
else if (id == "KZ")
{
ViewBag.MyString = "KwaZulu-Natal";
}
else if (id == "LP")
{
ViewBag.MyString = "Limpopo";
}
else if (id == "MP")
{
ViewBag.MyString = "Mpumalanga";
}
else if (id == "NC")
{
ViewBag.MyString = "Northern Cape";
}
else if (id == "NW")
{
ViewBag.MyString = "North West";
}
else
{
ViewBag.MyString = "Western Cape";
}
String Excluded = "SELECT REG_END, COUNT(*) FROM GENERAL WHERE HOME_PROVINCE = '" + prov + "' AND YOS3_OUT = 'Excluded' GROUP BY REG_END ORDER BY REG_END";
String Qualified = "SELECT REG_END, COUNT(*) FROM GENERAL WHERE HOME_PROVINCE = '" + prov + "' AND YOS3_OUT = 'Qualified' GROUP BY REG_END ORDER BY REG_END";
String Proceed = "SELECT REG_END, COUNT(*) FROM GENERAL WHERE HOME_PROVINCE = '" + prov + "' AND YOS3_OUT = 'Proceed' GROUP BY REG_END ORDER BY REG_END";
String NotCategorised = "SELECT REG_END, COUNT(*) FROM GENERAL WHERE HOME_PROVINCE = '" + prov + "' AND YOS3_OUT = 'Not Categorised' GROUP BY REG_END ORDER BY REG_END";
string Male = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE GENDER='M' AND HOME_PROVINCE ='" + prov + "' GROUP BY YOS3_OUT ORDER BY YOS3_OUT";
string Female = "SELECT YOS3_OUT, COUNT(*) FROM GENERAL WHERE GENDER='F' AND HOME_PROVINCE ='" + prov + "' GROUP BY YOS3_OUT ORDER BY YOS3_OUT";
conn.Open();
if ((conn.State & System.Data.ConnectionState.Open) > 0)
{
cmd = new SqliteCommand(Excluded, conn);
SqliteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupProv obj = new LookupProv();
obj.reg_End = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupExcluded.Add(obj);
}
cmd = new SqliteCommand(Qualified, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupProv obj = new LookupProv();
obj.reg_End = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupQualified.Add(obj);
}
cmd = new SqliteCommand(Proceed, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupProv obj = new LookupProv();
obj.reg_End = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupProceed.Add(obj);
}
cmd = new SqliteCommand(NotCategorised, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupProv obj = new LookupProv();
obj.reg_End = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupNotCategorised.Add(obj);
}
cmd = new SqliteCommand(Male, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupMale.Add(obj);
}
cmd = new SqliteCommand(Female, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
LookupOutcome obj = new LookupOutcome();
obj.outcome = reader.GetValue(0).ToString();
obj.count = reader.GetInt32(1);
groupFemale.Add(obj);
}
}
conn.Close();
ViewBag.Excluded = groupExcluded;
ViewBag.Qualified = groupQualified;
ViewBag.Proceed = groupProceed;
ViewBag.NotCategorised = groupNotCategorised;
ViewBag.Male = groupMale;
ViewBag.Female = groupFemale;
return(View());
}
