public void Vote(CreateVoteModel createVoteModel)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
var service = new UserService();
int questionID = GetQuestionID(createVoteModel.FldQuestiontext);
int judgePairID = GetJudgePairID(createVoteModel.FldJudgeUsername);
// todo validate above + FldTeamName
// New Vote and get voteID returned
int voteID = 0;
TblVote vote = TryFindVote(questionID, judgePairID, createVoteModel.FldTeamName);
if (vote == null)
{
voteID = CreateVote(judgePairID, createVoteModel.FldTeamName, createVoteModel.FldPoints);
CreateVoteAnswer(questionID, voteID);
}
else
{
UpdateVote(vote.FldVoteId, judgePairID, createVoteModel.FldTeamName, createVoteModel.FldPoints);
}
// Just for test of exceptionhandling. TODO remove
throw new Exception("øv");
}
}
public void CreateJudge(int eventID, string judgeUsername, string judgePassword, string judgeName)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
// Should find judge or school by username, instead of searching all though
foreach (TblJudge j in GetAllJudges())
{
if (judgeUsername.Equals(j.FldJudgeUsername))
{
throw new UserNameAlreadyExistsException(judgeUsername);
}
}
foreach (TblSchool s in GetAllSchools())
{
if (judgeUsername.Equals(s.FldSchoolUsername))
{
throw new UserNameAlreadyExistsException(judgeUsername);
}
}
TblJudge judge = new TblJudge()
{
FldEventId = eventID,
FldJudgeUsername = judgeUsername,
FldJudgePassword = judgePassword,
FldJudgeName = judgeName
};
databaseContext.TblJudge.Add(judge);
databaseContext.SaveChanges();
}
}
public TblTeam GetSpecificTeam(string teamName)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
return(databaseContext.TblTeam.Find(teamName));
}
}
private TblJudge GetJudgeByID(int fldJudgeId)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
return(databaseContext.TblJudge.Find(fldJudgeId));
}
}
public void DeleteSchool(TblSchool school)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
databaseContext.TblSchool.Remove(school);
databaseContext.SaveChanges();
}
}
public void DeleteJudge(TblJudge judge)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
databaseContext.TblJudge.Remove(judge);
databaseContext.SaveChanges();
}
}
private List <TblQuestion> FindQuestions(DB_YoungEnterpriseContext databaseContext, string questionCategory, string questionSubject)
{
List <TblQuestion> questions = new List <TblQuestion>();
foreach (TblQuestion question in databaseContext.TblQuestion.Where(q => q.FldQuestionCategori.Equals(questionCategory) && q.FldQuestionSubject.Equals(questionSubject)))
{
questions.Add(question);
}
return(questions);
}
private void DeleteAllExceptQuestions(DB_YoungEnterpriseContext databaseContext)
{
databaseContext.TblVoteAnswer.RemoveRange(databaseContext.TblVoteAnswer);
databaseContext.TblVote.RemoveRange(databaseContext.TblVote);
databaseContext.TblJudgePair.RemoveRange(databaseContext.TblJudgePair);
databaseContext.TblJudge.RemoveRange(databaseContext.TblJudge);
databaseContext.TblTeam.RemoveRange(databaseContext.TblTeam);
databaseContext.TblSchool.RemoveRange(databaseContext.TblSchool);
databaseContext.TblEvent.RemoveRange(databaseContext.TblEvent);
databaseContext.SaveChanges();
}
public DB_YoungEnterpriseContext GetConnection()
{
//DESKTOP-ACNIRC0 Louise
//DESKTOP-6D9EMB1 Mikkel
var connection = @"Server=DESKTOP-6D9EMB1;Database=DB_YoungEnterprise;Trusted_Connection=True;";
var optionsBuilder = new DbContextOptionsBuilder <DB_YoungEnterpriseContext>();
optionsBuilder.UseSqlServer(connection);
DB_YoungEnterpriseContext context = new DB_YoungEnterpriseContext(optionsBuilder.Options);
return(context);
}
public List <TblQuestion> GetAllQuestions()
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
List <TblQuestion> allQuestions = new List <TblQuestion>();
foreach (TblQuestion question in databaseContext.TblQuestion)
{
allQuestions.Add(question);
}
return(allQuestions);
}
}
public List <TblTeam> GetAllTeams()
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
List <TblTeam> allTeams = new List <TblTeam>();
foreach (TblTeam team in databaseContext.TblTeam)
{
allTeams.Add(team);
}
return(allTeams);
}
}
public List <TblSchool> GetAllSchools()
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
List <TblSchool> allSchools = new List <TblSchool>();
foreach (TblSchool school in databaseContext.TblSchool)
{
allSchools.Add(school);
}
return(allSchools);
}
}
public List <TblJudgePair> GetAllJudgePairs()
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
List <TblJudgePair> allJudges = new List <TblJudgePair>();
foreach (TblJudgePair judgePair in databaseContext.TblJudgePair)
{
allJudges.Add(judgePair);
}
return(allJudges);
}
}
private void CreateVoteAnswer(int questionID, int voteID)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
TblVoteAnswer voteAnswer = new TblVoteAnswer()
{
FldQuestionId = questionID,
FldVoteId = voteID
};
databaseContext.TblVoteAnswer.Add(voteAnswer);
databaseContext.SaveChanges();
}
}
public int CreateEvent(DateTime dateTime)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
DeleteAllExceptQuestions(databaseContext);
TblEvent tblEvent = new TblEvent()
{
FldEventDate = dateTime
};
databaseContext.TblEvent.Add(tblEvent);
databaseContext.SaveChanges();
return(tblEvent.FldEventId);
}
}
public void CreateTeam(string teamName, int schoolID, string subject, byte[] report)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
TblTeam team = new TblTeam()
{
FldTeamName = teamName,
FldSchoolId = schoolID,
FldSubjectCategory = subject,
FldReport = report
};
databaseContext.TblTeam.Add(team);
databaseContext.SaveChanges();
}
}
public TblEvent GetCurrentEvent()
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
// There should only be one event at a time, hence why this method also returns just the first event index.
List <TblEvent> allEvents = new List <TblEvent>();
foreach (TblEvent ev in databaseContext.TblEvent)
{
allEvents.Add(ev);
}
return(allEvents[0]);
}
}
private void CreateJudgePair(int judgeIdA, int judgeIdB)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
TblJudgePair judgePair = new TblJudgePair()
{
FldJudgeIda = judgeIdA,
//FldJudgeIdaNavigation = judgeNavA,
FldJudgeIdb = judgeIdB
//FldJudgeIdbNavigation = judgeNavB
};
databaseContext.TblJudgePair.Add(judgePair);
databaseContext.SaveChanges();
}
}
private int CreateVote(int judgePairID, string teamName, int points)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
TblVote vote = new TblVote()
{
FldJudgePairId = judgePairID,
FldTeamName = teamName,
FldPoints = points
};
databaseContext.TblVote.Add(vote);
databaseContext.SaveChanges();
return(vote.FldVoteId);
}
}
private void UpdateVote(int voteID, int judgePairID, string teamName, int points)
{
TblVote vote = null;
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
// Get existing vote using context
vote = databaseContext.TblVote.Where(v => v.FldVoteId == voteID).FirstOrDefault <TblVote>();
// Change values
if (vote != null)
{
vote.FldJudgePairId = judgePairID;
vote.FldTeamName = teamName;
vote.FldPoints = points;
}
// save changes using context.
databaseContext.SaveChanges();
}
}
public List <TblVoteAnswer> FindQuestionsAndVotes(string questionCategory, string questionSubject, int judgePairId, string teamName)
{
List <TblVoteAnswer> result = new List <TblVoteAnswer>();
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
foreach (TblQuestion question in FindQuestions(databaseContext, questionCategory, questionSubject))
{
TblVote vote = FindJudgePairVotes(question.FldQuestionId, judgePairId, teamName);
TblVoteAnswer voteAnswer = new TblVoteAnswer
{
FldQuestionId = question.FldQuestionId,
Questiontext = question.FldQuestionText,
QuestionModifier = question.FldQuestionModifier,
FldVoteId = vote == null ? 0 : vote.FldVoteId,
Points = vote == null ? 0 : vote.FldPoints
};
result.Add(voteAnswer);
}
}
return(result);
}
public double FindTeamVotes(int questionID, string teamName)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
double result = 0;
var voteAnswer = databaseContext.TblVoteAnswer
.Where(va =>
va.FldQuestion.FldQuestionId == questionID &&
va.FldVote.FldTeamName == teamName
)
.FirstOrDefault();
if (voteAnswer == null)
{
result = 0;
}
else
{
databaseContext.Entry(voteAnswer).Reference(va => va.FldVote).Load();
result = voteAnswer.FldVote.FldPoints;
}
return(result);
}
}
public TblVote FindJudgePairVotes(int questionID, int judgePairId, string teamName)
{
using (DB_YoungEnterpriseContext databaseContext = GetConnection())
{
TblVote result = null;
var voteAnswer = databaseContext.TblVoteAnswer
.Where(va =>
va.FldQuestion.FldQuestionId == questionID &&
va.FldVote.FldJudgePairId == judgePairId &&
va.FldVote.FldTeamName == teamName
)
.FirstOrDefault();
if (voteAnswer == null)
{
result = null;
}
else
{
databaseContext.Entry(voteAnswer).Reference(va => va.FldVote).Load();
result = voteAnswer.FldVote;
}
return(result);
}
}
public VoteService(DB_YoungEnterpriseContext context)
{
_context = context;
}
