public int CreateTeam(Team team, IEnumerable <string> memberEmails)
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var teamId = conn.ExecuteScalar <int>(
@"
insert into Teams values (@Name, @CreatedBy)
declare @teamId int = scope_identity();
insert into TeamMembers values (@teamId, @CreatedBy)
select @teamId",
team);
var teamMembers = memberEmails
.Select(email => new
{
TeamId = teamId,
Email = email
});
conn.Execute(
@"
insert into TeamMembers(TeamId, UserId)
select @TeamId, Id from Users
where email = @email and not exists (
select 1 from TeamMembers tm where tm.UserId = Id and tm.TeamId = @TeamId)" ,
teamMembers);
return(teamId);
}
}
public void CreateUser(User user)
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var command = "insert into dbo.Users (Username, Email, PasswordHash) values(@Username, @Email, @PasswordHash)";
conn.Execute(command, user);
}
}
public IEnumerable <User> GetUsers()
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var users = conn.Query <User>("select * from users");
return(users);
}
}
public int GetCardPoints(int cardId, int userId)
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var points = conn.QueryFirstOrDefault <int>(
@"select top 1 Points from Points where CardId = @cardId and PointedBy = @userId
order by DateCreated desc",
new { cardId, userId });
return(points);
}
}
public IEnumerable <Team> GetTeamsByUser(int memberUserId)
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var teams = conn.Query <Team>(
@"select [Id], [Name], [CreatedBy]
from Teams t
inner join TeamMembers tm on t.Id = tm.TeamId
where tm.UserId = @userId",
new { userId = memberUserId });
return(teams);
}
}
public int CreateCard(Card card)
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var command =
@"insert into Cards
(Description, CreatedBy, ClosedBy, TeamId)
values (@Description, @CreatedBy, @ClosedBy, @TeamId)
select scope_identity()";
var id = conn.ExecuteScalar <int>(command, card);
return(id);
}
}
public int CreatePoint(Point point)
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var id = conn.ExecuteScalar <int>(
@"insert into Points (PointedBy, CardId, Points) values (@PointedBy, @CardId, @Points)
select scope_identity()",
point);
return(id);
}
}
public bool DoesCardCreatorExist(int creatorId)
{
using (var conn = _connectionProvider.GetOpenPointingPokerConnection())
{
var query = @"
select
case when
exists(
select 1
from Users
where Id = @creatorId)
then cast(1 as bit)
else cast(0 as bit)
end as [exists]";
var exists = conn.QueryFirst <bool>(query, new { creatorId });
return(exists);
}
}