347 lines
14 KiB
C#
347 lines
14 KiB
C#
using Dapper;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Linq;
|
|
using System.Text;
|
|
using TrackerLibrary.Models;
|
|
|
|
|
|
//@PlaceNumber int,
|
|
//@PlaceName nvarchar(50),
|
|
//@PrizeAmount money,
|
|
// @PrizePercentage float,
|
|
//@id int = 0 output
|
|
|
|
namespace TrackerLibrary.DataAccess
|
|
{
|
|
public class SqlConnector : IDataConnection
|
|
{
|
|
private const string db = "Tournaments";
|
|
public void CreatePerson(PersonModel model)
|
|
{
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@FirstName", model.FirstName);
|
|
p.Add("@LastName", model.LastName);
|
|
p.Add("@EmailAddress", model.EmailAddress);
|
|
p.Add("@CellPhoneNumber", model.CellPhoneNumber);
|
|
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spPeople_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
model.Id = p.Get<int>("@Id");
|
|
}
|
|
}
|
|
|
|
// TODO - Make the CreatePrize method actually save to the database
|
|
/// <summary>
|
|
/// Saves a prize to the database
|
|
/// </summary>
|
|
/// <param name="model">The prize information.</param>
|
|
/// <returns>The prize information, including the unique identifier.</returns>
|
|
public void CreatePrize(Models.PrizeModel model)
|
|
{
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@PlaceNumber", model.PlaceNumber);
|
|
p.Add("@PlaceName", model.PlaceName);
|
|
p.Add("@PrizeAmount", model.PrizeAmount);
|
|
p.Add("@PrizePercentage", model.PrizePercentage);
|
|
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spPrizes_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
model.Id = p.Get<int>("@Id");
|
|
|
|
}
|
|
}
|
|
|
|
public void CreateTeam(TeamModel model)
|
|
{
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@TeamName", model.TeamName);
|
|
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spTeams_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
model.Id = p.Get<int>("@Id");
|
|
|
|
foreach (PersonModel tm in model.TeamMembers)
|
|
{
|
|
p = new DynamicParameters();
|
|
p.Add("@TeamId", model.Id);
|
|
p.Add("@PersonId", tm.Id);
|
|
|
|
connection.Execute("dbo.spTeamMembers_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
}
|
|
|
|
}
|
|
|
|
}
|
|
|
|
public void CreateTournament(TournamentModel model)
|
|
{
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
SaveTournament(model, connection);
|
|
|
|
SaveTournamentPrizes(model, connection);
|
|
|
|
SaveTournamentEntries(model, connection);
|
|
|
|
SaveTournamentRounds(model, connection);
|
|
|
|
TournamentLogic.UpdateTournamentResults(model);
|
|
}
|
|
}
|
|
|
|
private void SaveTournament(TournamentModel model, IDbConnection connection)
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@TournamentName", model.TournamentName);
|
|
p.Add("@EntryFee", model.EntryFee);
|
|
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spTournaments_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
model.Id = p.Get<int>("@Id");
|
|
}
|
|
|
|
private static void SaveTournamentEntries(TournamentModel model, IDbConnection connection)
|
|
{
|
|
foreach (TeamModel tm in model.EnteredTeams)
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@TournamentId", model.Id);
|
|
p.Add("@TeamId", tm.Id);
|
|
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spTournamentEntries_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
}
|
|
}
|
|
|
|
private static void SaveTournamentRounds(TournamentModel model, IDbConnection connection)
|
|
{
|
|
// List<List<MatchupModel>> rounds
|
|
// List<MatchupEntryModel> Entries
|
|
|
|
// Loop through the rounds
|
|
// Loop through the matchups
|
|
// Save the matchup
|
|
// Loop through the entries and save them
|
|
|
|
foreach (List<MatchupModel> round in model.Rounds)
|
|
{
|
|
foreach (MatchupModel matchup in round)
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@TournamentId", model.Id);
|
|
p.Add("@MatchupRound", matchup.MatchupRound);
|
|
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spMatchups_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
matchup.Id = p.Get<int>("@id");
|
|
|
|
foreach (MatchupEntryModel entry in matchup.Entries)
|
|
{
|
|
|
|
p = new DynamicParameters();
|
|
p.Add("@MatchupId", matchup.Id);
|
|
if (entry.ParentMatchup == null)
|
|
{
|
|
p.Add("@ParentMatchupId", null);
|
|
}
|
|
else
|
|
{
|
|
p.Add("@ParentMatchupId", entry.ParentMatchup.Id);
|
|
}
|
|
if (entry.TeamCompeting == null)
|
|
{
|
|
p.Add("@TeamCompetingId", null);
|
|
}
|
|
else
|
|
{
|
|
p.Add("@TeamCompetingId", entry.TeamCompeting.Id);
|
|
}
|
|
p.Add("@id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spMatchupEntries_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
entry.Id = p.Get<int>("@id");
|
|
}
|
|
|
|
}
|
|
}
|
|
|
|
}
|
|
|
|
private static void SaveTournamentPrizes(TournamentModel model, IDbConnection connection)
|
|
{
|
|
foreach (PrizeModel pz in model.Prizes)
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@TournamentId", model.Id);
|
|
p.Add("@PrizeId", pz.Id);
|
|
p.Add("@Id", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
|
|
|
|
connection.Execute("dbo.spTournamentPrizes_Insert", p, commandType: CommandType.StoredProcedure);
|
|
|
|
}
|
|
}
|
|
|
|
public List<PersonModel> GetPerson_All()
|
|
{
|
|
|
|
List<PersonModel> output;
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
output = connection.Query<PersonModel>("dbo.spPeople_GetAll").ToList();
|
|
}
|
|
return output;
|
|
}
|
|
|
|
public List<TeamModel> GetTeam_All()
|
|
{
|
|
List<TeamModel> output;
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
output = connection.Query<TeamModel>("dbo.spTeam_GetAll").ToList();
|
|
|
|
foreach (TeamModel team in output)
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@TeamId", team.Id);
|
|
team.TeamMembers = connection.Query<PersonModel>("dbo.spTeamMembers_GetByTeam", p, commandType: CommandType.StoredProcedure).ToList();
|
|
}
|
|
}
|
|
return output;
|
|
}
|
|
|
|
public List<PrizeModel> GetPrizes_All()
|
|
{
|
|
List<PrizeModel> output;
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
output = connection.Query<PrizeModel>("dbo.spPrizes_GetAll").ToList();
|
|
}
|
|
return output;
|
|
}
|
|
|
|
public List<TournamentModel> GetTournament_All()
|
|
{
|
|
List<TournamentModel> output;
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
output = connection.Query<TournamentModel>("dbo.spTournaments_GetAll").ToList();
|
|
var p = new DynamicParameters();
|
|
foreach (TournamentModel t in output)
|
|
{
|
|
// Populate prizes
|
|
p = new DynamicParameters();
|
|
p.Add("@TournamentId", t.Id);
|
|
t.Prizes = connection.Query<PrizeModel>("dbo.spPrizes_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
|
|
// Populate Teams
|
|
t.EnteredTeams = connection.Query<TeamModel>("dbo.spTeam_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
|
|
foreach (TeamModel team in t.EnteredTeams)
|
|
{
|
|
p = new DynamicParameters();
|
|
p.Add("@TeamId", team.Id);
|
|
team.TeamMembers = connection.Query<PersonModel>("dbo.spTeamMembers_GetByTeam", p, commandType: CommandType.StoredProcedure).ToList();
|
|
}
|
|
// Populate Rounds
|
|
p = new DynamicParameters();
|
|
p.Add("@TournamentId", t.Id);
|
|
List<MatchupModel> matchups = connection.Query<MatchupModel>("dbo.spMatchups_GetByTournament", p, commandType: CommandType.StoredProcedure).ToList();
|
|
foreach (MatchupModel m in matchups)
|
|
{
|
|
p = new DynamicParameters();
|
|
p.Add("@MatchupId", m.Id);
|
|
m.Entries = connection.Query<MatchupEntryModel>("dbo.spMatchupEntries_GetByMatchup", p, commandType: CommandType.StoredProcedure).ToList();
|
|
|
|
List<TeamModel> allTeams = GetTeam_All();
|
|
|
|
if (m.WinnerId > 0)
|
|
{
|
|
m.Winner = allTeams.Where(x => x.Id == m.WinnerId).First();
|
|
}
|
|
|
|
foreach (var me in m.Entries)
|
|
{
|
|
if (me.TeamCompetingId > 0)
|
|
{
|
|
me.TeamCompeting = allTeams.Where(x => x.Id == me.TeamCompetingId).First();
|
|
}
|
|
if (me.ParentMatchupId > 0)
|
|
{
|
|
me.ParentMatchup = matchups.Where(x => x.Id == me.ParentMatchupId).First();
|
|
}
|
|
}
|
|
}
|
|
|
|
// List<List<MatchupModel>>
|
|
List<MatchupModel> currRow = new List<MatchupModel>();
|
|
int currRound = 1;
|
|
foreach (MatchupModel m in matchups)
|
|
{
|
|
if (m.MatchupRound > currRound)
|
|
{
|
|
t.Rounds.Add(currRow);
|
|
currRow = new List<MatchupModel>();
|
|
currRound += 1;
|
|
}
|
|
currRow.Add(m);
|
|
}
|
|
|
|
t.Rounds.Add(currRow);
|
|
}
|
|
}
|
|
return output;
|
|
}
|
|
|
|
public void UpdateMatchup(MatchupModel model)
|
|
{
|
|
using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
var p = new DynamicParameters();
|
|
if (model.Winner != null)
|
|
{
|
|
p.Add("@Id", model.Id);
|
|
p.Add("@WinnerId", model.Winner.Id);
|
|
|
|
connection.Execute("dbo.spMatchups_Update", p, commandType: CommandType.StoredProcedure);
|
|
}
|
|
foreach (MatchupEntryModel m in model.Entries)
|
|
{
|
|
if (m.TeamCompeting != null)
|
|
{
|
|
p = new DynamicParameters();
|
|
p.Add("@Id", m.Id);
|
|
p.Add("@TeamCompetingId", m.TeamCompeting.Id);
|
|
p.Add("@Score", m.Score);
|
|
connection.Execute("dbo.spMatchupEntries_Update", p, commandType: CommandType.StoredProcedure);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
public void CompleteTournament(TournamentModel model)
|
|
{
|
|
// spTournaments_Complete
|
|
using(IDbConnection connection = new System.Data.SqlClient.SqlConnection(GlobalConfig.CnnString(db)))
|
|
{
|
|
var p = new DynamicParameters();
|
|
p.Add("@Id", model.Id);
|
|
connection.Execute("dbo.spTournaments_Complete", p, commandType: CommandType.StoredProcedure);
|
|
}
|
|
}
|
|
}
|
|
}
|