public ArrayList GoogleGraph()
{
string competitor_query = "SELECT COUNT(*) FROM User WHERE role = 'competitor'";
string club_query = "SELECT COUNT(*) FROM User WHERE role = 'club'";
string judge_query = "SELECT COUNT(*) FROM User WHERE role = 'judge'";
int obj = new int();
int numcompetitors = SqliteDataAccess.SingleObject <int>(obj, competitor_query);
int numclubs = SqliteDataAccess.SingleObject <int>(obj, club_query);
int numjudges = SqliteDataAccess.SingleObject <int>(obj, judge_query);
ArrayList header = new ArrayList {
"title", "title2"
};
ArrayList data1 = new ArrayList {
"Club Admins", numclubs
};
ArrayList data2 = new ArrayList {
"Judges", numjudges
};
ArrayList data3 = new ArrayList {
"Competitors", numcompetitors
};
ArrayList data = new ArrayList {
header, data1, data2, data3
};
return(data);
}
public string GetUsersStats()
{
string competitor_query = "SELECT COUNT(*) FROM User WHERE role = 'competitor'";
string club_query = "SELECT COUNT(*) FROM User WHERE role = 'club'";
string judge_query = "SELECT COUNT(*) FROM User WHERE role = 'judge'";
int obj = new int();
int numcompetitors = SqliteDataAccess.SingleObject <int>(obj, competitor_query);
int numclubs = SqliteDataAccess.SingleObject <int>(obj, club_query);
int numjudges = SqliteDataAccess.SingleObject <int>(obj, judge_query);
StringBuilder UserRoleStatsHTML = new StringBuilder();
UserRoleStatsHTML.Append("<p>Competitors: ");
UserRoleStatsHTML.Append(numcompetitors);
UserRoleStatsHTML.Append("</p>");
UserRoleStatsHTML.Append("<p>Clubs: ");
UserRoleStatsHTML.Append(numclubs);
UserRoleStatsHTML.Append("</p>");
UserRoleStatsHTML.Append("<p>Judges: ");
UserRoleStatsHTML.Append(numjudges);
UserRoleStatsHTML.Append("</p>");
return(UserRoleStatsHTML.ToString());
}
//Adds member to eventcompetitor if club is in eventclub table
public ActionResult EnrollMember(CompetitorModel comp)
{
//checks if club is allowed to submit members for said event
EnrolledUserModel obj = new EnrolledUserModel();
obj.eventID = comp.eventID;
obj.userID = (int)HttpContext.Session.GetInt32("UserID");
string query1 = "select * from eventclub as ec join club as c on ec.clubID = c.clubID where userID = @userID and ec.eventID = @eventID";
Console.WriteLine("1. " + comp.eventID + " " + comp.userID);
obj = SqliteDataAccess.SingleObject(obj, query1);
if (obj == null)
{
Console.WriteLine("NULL");
return(RedirectToAction("Application", "Home"));
}
else
{
CompetitorModel competitor = new CompetitorModel();
competitor.userID = comp.userID;
string query3 = "select * from competitor where userID = @userID";
competitor = SqliteDataAccess.SingleObject(competitor, query3);
competitor.eventID = comp.eventID;
string query2 = "insert into eventcompetitor(eventID, competitorID) values(@eventID, @competitorID)";
SqliteDataAccess.SaveSingleObject(competitor, query2);
for (int i = 1; i < 7; i++)
{
CompetitorModel comp2 = new CompetitorModel();
comp2.eventID = comp.eventID;
comp2.competitorID = competitor.competitorID;
comp2.jumpnr = i;
competitorList.Add(comp2);
}
string query4 = "insert into jump(eventID, competitorID, jumpnr) values(@eventID, @competitorID, @jumpnr)";
SqliteDataAccess.SaveManyObjects(competitorList, query4);
}
return(RedirectToAction("Application", "Home"));
}
//returns clubinfo to competitor in view application
public ActionResult CheckClub()
{
string query = "select c.clubID, c.clubname from club as c join user as u on c.clubID = u.club where u.userID = @userID";
string clubinfo;
ClubModel club = new ClubModel();
club.userID = (int)HttpContext.Session.GetInt32("UserID");
club = SqliteDataAccess.SingleObject(club, query);
if (club != null)
{
clubinfo = club.clubname + " ID:" + club.clubID;
}
else
{
clubinfo = "No current club";
}
ViewBag.ClubInfo = clubinfo;
return(View("~/Views/Home/Application.cshtml"));
}
public ActionResult CreateClub(ClubModel club)
{
if (club.clubname == "NULL" || club.clubname == "Null" || club.clubname == "null")
{
Console.WriteLine("Input text null");
return(RedirectToAction("Managment", "Home"));
}
ClubModel replica = new ClubModel();
string query_replica = "select * from club where clubname = @clubname";
replica = SqliteDataAccess.SingleObject(club, query_replica);
if (replica != null)
{
Console.WriteLine("Club already exists");
return(RedirectToAction("Managment", "Home"));
}
string query = "insert into club(userID, clubname) values(@userId, @clubname)";
SqliteDataAccess.SaveSingleObject(club, query);
Console.WriteLine("Club created");
return(RedirectToAction("Managment", "Home"));
}
//Club applies to participate in an event
public ActionResult EventApplication(string eventID)
{
string query1 = "select * from club where userID = @userID";
ClubModel club = new ClubModel();
club.userID = (int)HttpContext.Session.GetInt32("UserID");
club = SqliteDataAccess.SingleObject(club, query1);
string query2 = "insert into eventapplication(clubID, eventID) values(@clubID, @eventID)";
EventApplicationModel application = new EventApplicationModel();
application.eventID = eventID;
application.clubID = club.clubID;
SqliteDataAccess.SaveSingleObject(application, query2);
return(RedirectToAction("Application", "Home"));
}
public ActionResult SetScore(int ContestantIDField, string JumpTypeField, float ScoreField)
{
//checks for input null from user
if (JumpTypeField == "null" || JumpTypeField == "Null" || JumpTypeField == "NULL")
{
return(RedirectToAction("Dashboard", "Home"));
}
//get upcoming event info based on judgeID/userID
//get the first jumpnumber that hasnt been scored by said judge for CompetitorID
//Assign score to that competitor
//Trigger check for finalscore
string query_next_event = "select e.eventID, j.judgeID, e.startdate, e.eventtype " +
"from event as e " +
"inner join eventjudge as ej on e.eventID = ej.eventID " +
"inner join judge as j on j.judgeID = ej.judgeID " +
"inner join user as u on u.userID = j.userID " +
"where u.userID=@userID and startdate >= date('now') " +
"order by startdate LIMIT 1";
ScoreModel eventinfo = new ScoreModel();
eventinfo.userID = (int)HttpContext.Session.GetInt32("UserID");
Console.WriteLine("userID: " + eventinfo.userID);
eventinfo = SqliteDataAccess.SingleObject(eventinfo, query_next_event);
Console.WriteLine("eventinfo: " + eventinfo.eventID + " " + eventinfo.judgeID + " Height:" + eventinfo.eventtype);
eventinfo.competitorID = ContestantIDField;
var eventID = eventinfo.eventID;
var judgeID = eventinfo.judgeID;
var height = eventinfo.eventtype;
int jumpID = 0;
//Only allows scoring for an event if the eventdate is today
DateTime thedate = eventinfo.startdate;
DateTime today = DateTime.Today;
// if (today == thedate)
if (true)
{
Console.WriteLine("IF! today: " + today + " startdate: " + thedate);
}
else
{
Console.WriteLine("Not Today! today: " + today + " startdate: " + eventinfo.startdate);
return(RedirectToAction("Scoring", "Home"));
}
//Fetches latest jump nr if a score exists
//if null assumse current jumpnr is 1
//else take the number and add 1 to it if it isnt already max number
string query_next_jump = "select j.jumpnr, j.jumpID from score as s inner join jump as j on j.jumpID = s.jumpID where eventID = @eventID and judgeID = @judgeID and competitorID = @competitorID order by jumpnr desc LIMIT 1";
eventinfo = SqliteDataAccess.SingleObject(eventinfo, query_next_jump);
// Console.WriteLine("current jump: " + eventinfo.jumpID);
string query_score = "";
string query_jump_type = "update jump set jumptype = @jumptype where jumpID=@jumpID";
//Seperates groupnr from style in jumptype. eg 101A -> groupnr: 101 style: A
int size = JumpTypeField.Length - 1;
string groupstr = "";
int i = 0;
int groupnr;
char style = new char();
foreach (char x in JumpTypeField)
{
if (x == 48 || x == 49 || x == 50 || x == 51 || x == 52 || x == 53 || x == 54 || x == 55 || x == 56 || x == 57)
{
groupstr += x;
}
else
{
style = x;
}
i++;
}
Console.Write(groupstr);
Console.Write(" - " + style);
Console.WriteLine("");
Console.WriteLine("grpstr: " + groupstr);
groupnr = Int32.Parse(groupstr);
Console.WriteLine("groupnr: " + groupnr);
//Get jumptypeID
JumpTypeModel jump = new JumpTypeModel();
jump.groupnr = groupnr;
jump.style = style;
jump.height = height;
string query_dd = "select * from jumptype where groupnr = @groupnr and height = @height and style = @style";
jump = SqliteDataAccess.SingleObject(jump, query_dd);
if (jump == null)
{
Console.WriteLine("Jumptype does not exist");
return(RedirectToAction("Scoring", "Home"));
}
int ID = jump.id;
var dd = jump.value;
if (eventinfo == null)
{
ScoreModel score_edge = new ScoreModel();
//insert score for jumpnr 1
Console.WriteLine("jump is null, assuming score is for jumpnr1 ");
//need to find out jumpid
score_edge.competitorID = ContestantIDField;
score_edge.eventID = eventID;
Console.WriteLine("eventID: " + score_edge.eventID + " judgeID: " + score_edge.judgeID + "competitorID " + score_edge.competitorID);
string query_get_jumpid = " select jumpID, eventID from jump where eventID = @eventID and competitorID = @competitorID order by jumpnr asc limit 1";
score_edge = SqliteDataAccess.SingleObject(score_edge, query_get_jumpid);
score_edge.competitorID = ContestantIDField;
score_edge.eventID = eventID;
score_edge.jumpnr = 1;
score_edge.judgeID = judgeID;
score_edge.score = ScoreField;
score_edge.jumptype = ID;
Console.WriteLine("JumpID:" + score_edge.jumpID);
jumpID = score_edge.jumpID;
query_score = "insert into score(jumpID, judgeID, score) values(@jumpID, @judgeID, @score)";
SqliteDataAccess.SaveSingleObject(score_edge, query_score);
Console.WriteLine("jmptype: " + score_edge.jumptype + " competitorID: " + score_edge.competitorID + " eventID: " + score_edge.eventID);
SqliteDataAccess.SaveSingleObject(score_edge, query_jump_type);
}
else if (eventinfo.jumpnr != 6)
{
query_score = "insert into score(jumpID, judgeID, score) values(@jumpID, @judgeID, @score)";
Console.WriteLine("jump is: " + eventinfo.jumpnr);
eventinfo.jumpnr++;
eventinfo.jumpID++;
eventinfo.score = ScoreField;
eventinfo.eventID = eventID;
eventinfo.judgeID = judgeID;
eventinfo.jumptype = ID;
eventinfo.competitorID = ContestantIDField;
jumpID = eventinfo.jumpID;
Console.WriteLine("Inserting to score: " + eventinfo.jumpID + " " + eventinfo.judgeID + " " + eventinfo.score);
Console.WriteLine("next is jump: " + eventinfo.jumpnr);
SqliteDataAccess.SaveSingleObject(eventinfo, query_score);
Console.WriteLine("jmptype: " + eventinfo.jumptype + " competitorID: " + eventinfo.competitorID + " eventID: " + eventinfo.eventID);
SqliteDataAccess.SaveSingleObject(eventinfo, query_jump_type);
//insert score for jumpnr
}
else if (eventinfo.jumpnr == 6)
{
Console.WriteLine("Error you have already scored all jumps for this competitor" + eventinfo.jumpnr);
}
FinalScore(jumpID, groupnr, style, height, dd);
return(RedirectToAction("Scoring", "Home"));
}
//gets the current/upcoming event for the judge
public ActionResult GetEvent()
{
string query = "select j.eventID, j.competitorID, u.fname," +
" u.lname, j.jumpnr, j.jumptype, us.fname as JudgeFirstName ," +
" us.lname as JudgeLastName, s.score, j.finalscore " +
"from jump as j " +
"left join score as s on j.jumpID = s.jumpID " +
"left join competitor as c on c.competitorID = j.competitorID " +
"left join user as u on u.userID = c.userID " +
"left join judge as ju on ju.judgeID = s.judgeID " +
"left join user as us on us.userID = ju.userID " +
"where j.eventID = @eventID " +
"order by j.competitorID and j.jumpnr";
EventJumpModel eventjump = new EventJumpModel();
eventjump.userID = (int)HttpContext.Session.GetInt32("UserID");
Console.WriteLine("userID: " + eventjump.userID);
StringBuilder jumpListHtml = new StringBuilder("<table id=\"jumpTbl\">" +
"<tr><th>Event Name</th><th>Competitor ID</th>" +
"<th>First Name</th><th>Last Name</th><th>Jump nr</th><th>Jump Type</th>" +
"<th>Judge First Name</th><th>Judge Last Name</th><th>Score</th><th>Final Score</th></tr>");
string query_original = "select j.jumpID, j.eventID, j.competitorID," +
" u.fname, u.lname, j.jumpnr, s.judgeID, s.score, j.finalscore" +
" from jump as j inner join score as s on j.jumpID = s.jumpID " +
"inner join competitor as c on c.competitorID = j.competitorID " +
"inner join user as u on u.userID = c.userID " +
"where j.eventID = @eventID group by j.jumpID, s.judgeID";
string query_upcoming_event = "SELECT e.eventID from event as e " +
"inner join eventjudge as ej on ej.eventID = e.eventID " +
"inner join judge as j on j.judgeID = ej.judgeID " +
"inner join user as u on u.userID = j.userID " +
"where u.userID=@userID and startdate>=date('now') " +
"order by startdate " +
"asc limit 1";
eventjump = SqliteDataAccess.SingleObject(eventjump, query_upcoming_event);
if (eventjump == null)
{
return(RedirectToAction("Scoring", "Home"));
}
Console.WriteLine("event: " + eventjump.eventID);
jumpObjList = SqliteDataAccess.LoadEventJumps(eventjump, query);
Console.WriteLine("list: " + jumpObjList);
int i = 1;
foreach (var jump in jumpObjList)
{
jumpListHtml.Append("<tr id=" + i + "><td>");
jumpListHtml.Append(eventjump.eventID);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.competitorID);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.fname);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.lname);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.jumpnr);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.jumptype);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.JudgeFirstName);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.JudgeLastName);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.score);
jumpListHtml.Append("</td><td>");
jumpListHtml.Append(jump.finalscore);
jumpListHtml.Append("</td></tr>");
}
jumpListHtml.Append("</table>");
TempData["eventjumps"] = jumpListHtml.ToString();
return(RedirectToAction("Scoring", "Home"));
}
public ActionResult ClubApplicationManagment(int userID, string button)
{
UserModel clubuser = new UserModel();
ClubModel club = new ClubModel();
UserModel user = new UserModel();
string query;
if (button == "refresh")
{
user.userID = (int)HttpContext.Session.GetInt32("UserID");
query = "select u.userID, u.fname, u.lname from user as u join clubapplication as ca on u.userID = ca.userID join club as c on c.clubID = ca.clubID where c.userID = @userID";
StringBuilder applicationListHtml = new StringBuilder("<table id=\"pplTbl\"><tr><th>User ID</th><th>First Name</th><th>Last Name</th></tr>");
users = SqliteDataAccess.LoadManyObjects(user, query);
foreach (var app in users)
{
applicationListHtml.Append("<tr><td>");
applicationListHtml.Append(app.userID);
applicationListHtml.Append("</td><td>");
applicationListHtml.Append(app.fname);
applicationListHtml.Append("</td><td>");
applicationListHtml.Append(app.lname);
applicationListHtml.Append("</td></tr>");
}
applicationListHtml.Append("</table>");
Console.WriteLine(applicationListHtml);
//ViewBag.ClubApps = applicationListHtml;
TempData["ClubApps"] = applicationListHtml.ToString();
}
else if (button == "accept" && userID != 0)
{
//check if userID + clubID exist in clubapplication
ClubModel exist = new ClubModel();
exist.userID = userID;
query = "select * from clubapplication where userID=@userID";
exist = SqliteDataAccess.SingleObject(exist, query);
if (exist == null)
{
Console.WriteLine("User has not applied to this club");
return(View("~/Views/Home/Application.cshtml"));
}
else
{
club.userID = (int)HttpContext.Session.GetInt32("UserID");
query = "select * from club where userID=@userID";
club = SqliteDataAccess.SingleObject(club, query);
user.userID = userID;
Console.WriteLine("user ID:" + user.userID);
user.club = club.clubID;
Console.WriteLine("user club:" + user.club);
query = "update user set club = @club where userID = @userID";
SqliteDataAccess.SaveSingleObject(user, query);
query = "delete from clubapplication where userID=@userID";
SqliteDataAccess.DenyApplication(user, query);
}
}
else if (button == "deny" && userID != 0)
{
query = "delete from clubapplication where userID=@userID";
user.userID = userID;
SqliteDataAccess.DenyApplication(user, query);
}
else
{
Console.WriteLine("Shit went wrong!");
}
return(RedirectToAction("Application", "Home"));
}
