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")); }