public Response AddComment(AddCommentRequest request)
{
var sql = $@"INSERT INTO FeedbackComments(FeedbackId, CreatedOn, PostedBy, PosterId, Comments, MembershipId)
VALUES(
(SELECT FeedbackId FROM Feedback WHERE Guid = @FeedbackGuid),
GetDate(),
@PostedBy,
{(request.Membership == Core.Membership.Coach ? "(SELECT CoachId FROM Coaches WHERE Guid = @PosterGuid)" : "(SELECT PlayerId FROM Players WHERE Guid = @PosterGuid)")},
@Comments,
@MembershipId
);";
var parameters = new DynamicParameters();
parameters.Add("@FeedbackGuid", request.FeedbackId);
parameters.Add("@PostedBy", request.PostedBy);
parameters.Add("@PosterGuid", request.PosterId);
parameters.Add("@Comments", request.Comment);
parameters.Add("@MembershipId", request.Membership);
using (var connection = connectionFactory.Connect())
{
connection.Open();
connection.Execute(sql, parameters);
}
return(Response.CreateSuccessResponse());
}
public IEnumerable <Squad> GetMemberSquads(Guid memberId, Membership membership)
{
using (var connection = connectionFactory.Connect())
{
string sql = $@"SELECT C.Guid AS ClubGuid, S.Guid AS SquadGuid, S.Name,
(SELECT COUNT(1) FROM Players PLA WHERE PLA.SquadId = S.SquadId AND (PLA.Deleted IS NULL OR PLA.Deleted = 0)) AS NumberOfPlayers,
S.YearBorn
FROM Squads S
INNER JOIN Clubs C ON C.ClubId = S.ClubId
{ (membership == Membership.Coach
? "INNER JOIN SquadCoaches SC ON SC.SquadId = S.SquadId AND SC.CoachId = (SELECT CoachId FROM Coaches WHERE Guid = @MemberGuid AND (Deleted IS NULL OR Deleted = 0))"
: "INNER JOIN Players P ON P.SquadId = S.SquadId AND P.Playerid = (SELECT PlayerId FROM Players WHERE Guid = @MemberGuid AND (Deleted IS NULL OR Deleted = 0))")
}";
var p = new DynamicParameters();
p.Add("@MemberGuid", memberId.ToString());
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
var squads = reader.Select <dynamic, Squad>(
row => new Squad(Guid.Parse(row.ClubGuid.ToString()), Guid.Parse(row.SquadGuid.ToString()))
{
Name = row.Name,
NumberOfPlayers = row.NumberOfPlayers,
YearBorn = row.YearBorn
}).ToList();
return(squads);
}
}
public Club GetClub(Guid clubId)
{
using (var connection = connectionFactory.Connect()) {
string sql = @"SELECT Guid, Name, Url, logoUrl FROM Clubs WHERE Guid = @Guid; ";
//SELECT S.Guid, S.Name FROM Squads S
// INNER JOIN Clubs C ON S.ClubId = C.ClubId
// WHERE C.Guid = @Guid;";
var p = new DynamicParameters();
p.Add("@Guid", clubId.ToString());
connection.Open();
using (var multi = connection.QueryMultiple(sql, p)) {
var club = multi.Read().Cast <IDictionary <string, object> >().Select(row =>
new Club(Guid.Parse(row["Guid"].ToString()))
{
Name = (string)row["Name"],
Url = (string)row["Url"],
LogoUrl = (string)row["LogoUrl"]
}
).FirstOrDefault();
//var squads = multi.Read().Cast<IDictionary<string, object>>().Select(row =>
// new Squad(club.Guid, Guid.Parse(row["Guid"].ToString())) {
// Name = (string)row["Name"]
//}).ToList();
//if(squads != null && squads.Count > 0)
// squads.ForEach((s) => club.AddSquad(s));
return(club);
}
}
}
public void CreateAssignment(AssignmentRequest request)
{
using (var connection = factory.Connect())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
var assignmentGuid = Guid.NewGuid();
CreateAssignment(assignmentGuid, request, connection, transaction);
AddTrainingMaterials(assignmentGuid, request.TrainingMaterials, connection, transaction);
if (request.AssignedTo == AssignedTo.SelectedSquads)
{
AssignToSquads(assignmentGuid, request, connection, transaction);
}
else
{
AssignToPlayers(assignmentGuid, request, connection, transaction);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
}
public TrainingMaterial GetTrainingMaterial(Guid trainingMaterialId)
{
if (trainingMaterialId.IsEmpty())
{
return(null);
}
using (var connection = connectionFactory.Connect()) {
string sql = @" SELECT T.Guid AS TrainingMaterialGuid, C.Guid AS ClubGuid, T.Title, T.Description, T.ContentType, T.ExternalId, T.Url, T.ThumbnailUrl
FROM TrainingMaterials T
INNER JOIN Clubs C ON C.ClubId = T.ClubId AND T.Guid = @TrainingMaterialGuid
WHERE T.Deleted IS NULL OR T.Deleted = 0" ;
DynamicParameters p = new DynamicParameters();
p.Add("@TrainingMaterialGuid", trainingMaterialId.ToString());
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
var material = reader.Select <dynamic, TrainingMaterial>(
row => new TrainingMaterial(Guid.Parse(row.ClubGuid.ToString()), Guid.Parse(row.TrainingMaterialGuid.ToString()))
{
ContentType = row.ContentType, Description = row.Description, ExternalId = row.ExternalId,
ThumbnailUrl = row.ThumbnailUrl, Title = row.Title, Url = row.Url
}).FirstOrDefault();
return(material);
}
}
public ReportCardDesign GetReportCardDesign(Guid clubId, Guid reportDesignId)
{
string sql = $@"SELECT R.Guid AS DesignGuid, C.Guid AS ClubGuid, R.Name
FROM ReportCardDesigns R
INNER JOIN Clubs C ON R.ClubId = C.ClubId AND C.Guid = @ClubGuid
WHERE (R.Deleted IS NULL OR R.Deleted = 0) AND R.Guid = @ReportDesignGuid";
DynamicParameters p = new DynamicParameters();
p.Add("@ClubGuid", clubId.ToString());
p.Add("@ReportDesignGuid", reportDesignId.ToString());
using (var connection = connectionFactory.Connect())
{
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
var design = reader.Select <dynamic, ReportCardDesign>(
row => new ReportCardDesign(Guid.Parse(row.ClubGuid.ToString()), Guid.Parse(row.DesignGuid.ToString()))
{
DesignName = row.Name
}).SingleOrDefault();
return(design);
}
}
public void AddTrainingMaterial(TrainingMaterial trainingMaterial)
{
ThrowIf.ArgumentIsNull(trainingMaterial);
using (var connection = connectionFactory.Connect()) {
string sql = GetInsertStatement();
DynamicParameters p = SetupInsertParameters(trainingMaterial);
connection.Open();
connection.Execute(sql, p);
}
}
public void AddCoach(Coach coach)
{
ThrowIf.ArgumentIsNull(coach);
using (var connection = connectionFactory.Connect()) {
string sql = GetInsertStatement();
DynamicParameters p = SetupParameters(coach);
connection.Open();
connection.Execute(sql, p);
}
}
public void AddPlayer(Player player)
{
ThrowIf.ArgumentIsNull(player);
using (var connection = connectionFactory.Connect())
{
string sql = GetInsertStatement();
DynamicParameters p = SetupInsertParameters(player);
connection.Open();
connection.Execute(sql, p);
}
memberQuery.UpdateCache();
}
public Player GetPlayer(Guid playerId)
{
if (playerId.IsEmpty())
{
return(null);
}
using (var connection = connectionFactory.Connect())
{
string sql = @"SELECT S.Guid AS SquadGuid, P.Guid AS PlayerGuid,
P.DateOfBirth, P.DominantFoot, P.FirstName, P.LastName,
P.Email, P.Nationality, P.SquadNumber
FROM Players P
INNER JOIN Squads S ON P.SquadId = S.SquadId
WHERE P.Guid = @Guid AND (P.Deleted IS NULL OR P.Deleted = 0)";
DynamicParameters p = new DynamicParameters();
p.Add("@Guid", playerId.ToString());
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
var player = reader.Select <dynamic, Player>(
row => new Player(Guid.Parse(row.SquadGuid.ToString()), Guid.Parse(row.PlayerGuid.ToString()))
{
DateOfBirth = DateTime.Parse(row.DateOfBirth.ToString()), DominantFoot = char.Parse(row.DominantFoot),
FirstName = row.FirstName, LastName = row.LastName, Email = row.Email,
Nationality = row.Nationality, SquadNumber = row.SquadNumber
}).FirstOrDefault();
return(player);
}
}
public void AddClub(Club club)
{
using (var connection = connectionFactory.Connect()) {
string sql = @"INSERT INTO CLUBS(Guid, Name, Url)
SELECT @Guid, @Name, @Url
WHERE NOT EXISTS (SELECT 1 FROM CLUBS WHERE Guid = @Guid)";
var p = new DynamicParameters();
p.Add("@Guid", club.Guid.ToString());
p.Add("@Name", club.Name);
p.Add("@Url", club.Url);
connection.Open();
connection.Execute(sql, p);
}
}
public void AddSquad(Squad squad)
{
using (var connection = connectionFactory.Connect()) {
string sql = @"INSERT INTO SQUADS(ClubId, Guid, Name, YearBorn)
SELECT C.ClubId, @SquadGuid, @Name, @YearBorn FROM CLUBS C
WHERE C.Guid = @ClubGuid";
var p = new DynamicParameters();
p.Add("@SquadGuid", squad.Guid.ToString());
p.Add("@Name", squad.Name);
p.Add("@ClubGuid", squad.ClubId.ToString());
p.Add("@YearBorn", squad.YearBorn);
connection.Open();
connection.Execute(sql, p);
}
}
private void SaveEvent(Event @event, SaveAction action)
{
using (var connection = connectionFactory.Connect()) {
connection.Open();
using (var transaction = connection.BeginTransaction()) {
try {
if (action == SaveAction.CREATE)
{
CreateEvent(@event, connection, transaction);
}
else if (action == SaveAction.UPDATE)
{
UpdateEvent(@event, connection, transaction);
}
UpdateSquadEvents(@event, connection, transaction);
UpdateEventTrainingMaterials(@event, connection, transaction);
transaction.Commit();
} catch (Exception ex) {
transaction.Rollback();
throw ex;
}
}
}
}
public void AddAvailability(NewAvailabilityRequest request)
{
using (var connection = connectionFactory.Connect()) {
string sql = @"INSERT INTO PlayerAvailability(PlayerId, AvailabilityId, DateFrom, DateTo, Notes)
SELECT PlayerId, @AvailabilityId, @DateFrom, @DateTo, @Notes
FROM Players P
INNER JOIN Squads S ON P.SquadId = S.SquadId
INNER JOIN Clubs C ON S.ClubId = C.ClubId
WHERE P.Guid = @PlayerGuid AND C.Guid = @ClubGuid AND (P.Deleted IS NULL OR P.Deleted = 0)" ;
var p = new DynamicParameters();
p.Add("@ClubGuid", request.ClubId.ToString());
p.Add("@PlayerGuid", request.PlayerId.ToString());
p.Add("@AvailabilityId", (short)request.AvailabilityStatus);
p.Add("@DateFrom", request.DateFrom);
p.Add("@DateTo", request.DateTo);
p.Add("@Notes", request.Notes);
connection.Open();
connection.Execute(sql, p);
}
}
public PlayerReportCard GetPlayerReportCard(Guid clubId, Guid reportCardId)
{
string sql = @"SELECT P.FirstName, P.LastName, P.SquadNumber, P.Guid AS PlayerGuid,
PRC.[Guid] AS PlayerReportCardGuid
FROM Players P
INNER JOIN Squads S ON P.SquadId = S.SquadId
INNER JOIN Clubs C ON C.ClubId = S.ClubId
LEFT JOIN[PlayerReportCards] PRC ON P.PlayerId = PRC.PlayerId
WHERE C.Guid = @ClubGuid AND PRC.Guid = @ReportCardId AND (P.Deleted IS NULL OR P.Deleted = 0)" ;
DynamicParameters p = new DynamicParameters();
p.Add("@ClubGuid", clubId.ToString());
p.Add("@ReportCardId", reportCardId.ToString());
using (var connection = factory.Connect())
{
connection.Open();
var reportCard = connection.Query(sql, p).Cast <IDictionary <string, object> >().Select <dynamic, PlayerReportCard>(
row => new PlayerReportCard(
Guid.Parse(row.PlayerGuid.ToString()),
(row.PlayerReportCardGuid != null ? Guid.Parse(row.PlayerReportCardGuid.ToString()) : null))
{
PlayerName = $"{row.FirstName} {row.LastName}",
SquadNumber = row.SquadNumber
}).SingleOrDefault();
return(reportCard);
}
}
public IEnumerable <MiniFeedback> GetFeedbackList(SquadFeedbackListRequest request)
{
DynamicParameters p = new DynamicParameters();
p.Add("@ClubGuid", request.ClubId.ToString());
p.Add("@SquadGuid", request.SquadId.ToString());
p.Add("@Year", request.Year);
p.Add("@Week", request.Week);
var sql = @"SELECT F.Guid AS FeedbackGuid, P.Guid AS PlayerGuid, P.FirstName + ' ' + P.LastName AS PlayerName, F.PublishedOn,
(SELECT TOP 1 '<strong>' + CAST(CreatedOn AS VARCHAR(20)) + ': ' + PostedBy + ' wrote</strong><br/>' + Comments FROM FeedbackComments FC WHERE FC.FeedbackId = F.FeedbackId ORDER BY CreatedOn DESC) AS LatestComment,
STUFF ((SELECT COALESCE(EA.Feedback + '<br/> ', '')
FROM EventAttendance EA
INNER JOIN Players P1 ON EA.PlayerId = P1.PlayerId
INNER JOIN Events E ON EA.EventId = E.EventId
WHERE YEAR(E.StartDate) = @Year AND DATEPART(wk, E.StartDate) = @Week AND P1.PlayerId = P.PlayerId
FOR XML PATH(''),TYPE ).value('.','VARCHAR(2000)')
,1, 0, '') AS WeeklyNotes
FROM Players P
INNER JOIN Squads S ON S.SquadId = P.SquadId
LEFT JOIN Feedback F ON P.PlayerId = F.PlayerId AND F.Year = @Year AND F.Week = @Week
WHERE (P.Deleted IS NULL OR P.Deleted = 0) AND S.Guid = @SquadGuid;";
using (var connection = connectionFactory.Connect())
{
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
var feedback = reader.Select <dynamic, MiniFeedback>(
row => new MiniFeedback((row.FeedbackGuid == null ? Guid.Empty : Guid.Parse(row.FeedbackGuid?.ToString())))
{
LatestComment = row.LatestComment,
Player = new MiniModel(Guid.Parse(row.PlayerGuid.ToString()), row.PlayerName),
PublishedOn = row.PublishedOn,
WeeklyNotes = row.WeeklyNotes
}).ToList();
return(feedback);
}
}
public IEnumerable <Availability> GetAvailabilities(GetAvailabilityRequest request)
{
bool includeSquadId = request.SquadId.HasValue && !request.SquadId.Value.IsEmpty();
bool includePlayerId = request.PlayerId.HasValue && !request.PlayerId.Value.IsEmpty();
var sql = GetAvailabilitiesSql(includeSquadId, includePlayerId, request.Year);
DynamicParameters p = new DynamicParameters();
p.Add("@ClubGuid", request.ClubId.ToString());
if (includeSquadId)
{
p.Add("@SquadGuid", request.SquadId.ToString());
}
if (includePlayerId)
{
p.Add("@PlayerGuid", request.PlayerId.ToString());
}
if (request.Year.HasValue)
{
p.Add("@Year", request.Year);
}
using (var connection = connectionFactory.Connect()) {
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
var availabilities = reader.Select <dynamic, Availability>(
row => new Availability(Guid.Parse(row.AvailabilityGuid.ToString()), Guid.Parse(row.PlayerGuid.ToString()))
{
AvailabilityStatus = (AvailabilityStatus?)row.AvailabilityId,
DateFrom = row.DateFrom, DateTo = (DateTime?)row.DateTo,
Notes = row.Notes, PlayerName = row.PlayerName, SquadName = row.SquadName
}).ToList();
return(availabilities);
}
}
public void Track(EventTrainingMaterialViewRequest request)
{
string sql = @" INSERT INTO EventTrainingMaterialViews (EventId, TrainingMaterialId, PlayerId, CoachId, ViewedOn)
VALUES(
(SELECT EventId FROM Events WHERE Guid = @EventGuid),
(SELECT TrainingMaterialId FROM TrainingMaterials WHERE Guid = @TrainingMaterialGuid),
(SELECT PlayerId FROM Players WHERE Guid = @PlayerGuid AND (Deleted IS NULL OR Deleted = 0)),
(SELECT CoachId FROM Coaches WHERE Guid = @CoachGuid),
GetDate()
)";
var parameters = new DynamicParameters();
parameters.Add("@EventGuid", request.EventId.ToString());
parameters.Add("@TrainingMaterialGuid", request.TrainingMaterialId.ToString());
parameters.Add("@PlayerGuid", request.Membership == Core.Membership.Player ? request.MemberId.ToString() : null);
parameters.Add("@CoachGuid", request.Membership == Core.Membership.Coach ? request.MemberId.ToString() : null);
using (var connection = connectionFactory.Connect())
{
connection.Open();
connection.Execute(sql, parameters);
}
}
public void AddTerm(TermSetupRequest request)
{
ThrowIf.ArgumentIsNull(request);
string sql = @"INSERT INTO EvaluationTerms (Guid, ClubId, Title, TermStatusId, StartDate, EndDate)
VALUES(
@TermGuid,
(SELECT ClubId FROM Clubs WHERE Guid = @ClubGuid),
@Title, 1, @StartDate, @EndDate
)" ;
DynamicParameters p = new DynamicParameters();
p.Add("@TermGuid", Guid.NewGuid().ToString());
p.Add("@ClubGuid", request.ClubId.ToString());
p.Add("@Title", request.Title);
p.Add("@StartDate", request.StartDate);
p.Add("@EndDate", request.EndDate);
using (var connection = connectionFactory.Connect()) {
connection.Open();
connection.Execute(sql, p);
}
}
public void AddReportDesign(ReportCardDesign reportDesign)
{
string sql = @"INSERT INTO ReportCardDesigns(Guid, Name, ClubId)
VALUES( @ReportCardDesignGuid, @Name,
(SELECT ClubId FROM Clubs WHERE Guid = @ClubGuid))";
DynamicParameters p = new DynamicParameters();
p.Add("@ReportCardDesignGuid", reportDesign.Guid.ToString());
p.Add("@Name", reportDesign.DesignName);
p.Add("@ClubGuid", reportDesign.ClubId.ToString());
using (var connection = connectionFactory.Connect())
{
connection.Open();
connection.Execute(sql, p);
}
}
private List <Item> Query(Guid clubdId)
{
using (var connection = connectionFactory.Connect()) {
string sql = @"SELECT S.Name,
S.Guid,
(SELECT COUNT(1) FROM Players P WHERE P.SquadId = S.SquadId AND (P.Deleted IS NULL OR P.Deleted = 0)) AS NumberOfPlayers,
(SELECT CO.FirstName + ' ' + CO.LastName
FROM SquadCoaches SC
INNER JOIN Coaches CO ON SC.SquadId = S.SquadId AND CO.CoachId = SC.CoachId
WHERE CO.Deleted IS NULL OR Co.Deleted = 0
) AS Coach
FROM Clubs C
INNER JOIN Squads S ON S.ClubId = C.ClubId
WHERE C.Guid = @ClubGuid
ORDER BY S.Name;" ;
var p = new DynamicParameters();
p.Add("@ClubGuid", clubdId.ToString());
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
return(reader.Select <dynamic, Item>(row => BuildItem(row)).ToList());
}
}
public IEnumerable <User> GetUsers(Guid clubId)
{
string sql = @"SELECT M.*, U.Id AS UserId, R.Name AS Role,
CAST (CASE WHEN U.LockoutEnd IS NOT NULL AND U.LockoutEnd > GetUTCDate() THEN 1 ELSE 0 END AS Bit) AS AccountLocked,
U.EmailConfirmed FROM
(SELECT P.Guid, P.FirstName, P.LastName, P.Email, P.DateOfBirth, 'Player' AS Membership
FROM Players P
INNER JOIN Squads S ON P.SquadId = S.SquadId
INNER JOIN Clubs C ON C.ClubId = S.ClubId
WHERE C.Guid = @ClubGuid AND (P.Deleted IS NULL OR P.Deleted = 0)
UNION ALL
SELECT CO.Guid, CO.FirstName, CO.LastName, CO.Email, CO.DateOfBirth, 'Coach' AS Membership
FROM Coaches CO
INNER JOIN Clubs CL ON CO.ClubId = CL.ClubId
WHERE CL.Guid = @ClubGuid AND (CO.Deleted IS NULL OR CO.Deleted = 0)) M
LEFT JOIN AspNetUsers U ON M.Email = U.Email
LEFT JOIN AspNetUserRoles UR ON U.Id = UR.UserId
LEFT JOIN AspNetRoles R ON UR.RoleId = R.Id
ORDER BY M.FirstName, M.LastName, M.Email" ;
var p = new DynamicParameters();
p.Add("@ClubGuid", clubId.ToString());
using (var connection = connectionFactory.Connect()) {
connection.Open();
var reader = connection.Query(sql, p).Cast <IDictionary <string, object> >();
var users = reader.Select <dynamic, User>(row => new User(row.UserId)
{
Email = row.Email,
Roles = row.Role,
Name = $"{row.FirstName} {row.LastName}",
AccountLocked = row.AccountLocked,
EmailConfirmed = row.EmailConfirmed,
}).ToList();
return(users);
}
}
public Event GetEvent(Guid eventId)
{
if (eventId.IsEmpty())
{
return(null);
}
using (var connection = connectionFactory.Connect()) {
string sql = @"SELECT C.Guid AS ClubGuid, E.Guid AS EventGuid, E.Title, E.StartDate, E.EndDate, E.Location, E.EventTypeId
FROM Events E
INNER JOIN Clubs C ON E.ClubId = C.ClubId AND E.Guid = @EventGuid
WHERE E.Deleted IS NULL OR E.Deleted = 0;
SELECT S.Guid AS SquadGuid, S.Name
FROM SquadEvents SE
INNER JOIN Events E ON SE.EventId = E.EventId AND E.Guid = @EventGuid
INNER JOIN Squads S ON SE.SquadId = S.SquadId
WHERE E.Deleted IS NULL OR E.Deleted = 0;
SELECT T.Guid AS TrainingMaterialGuid, T.Title, T.ContentType, T.ThumbnailUrl, T.Url, T.ExternalId, T.Description
FROM EventTrainingMaterials ETM
INNER JOIN Events E ON ETM.EventId = E.EventId AND E.Guid = @EventGuid
INNER JOIN TrainingMaterials T ON ETM.TrainingMaterialId = T.TrainingMaterialId
WHERE E.Deleted IS NULL OR E.Deleted = 0;" ;
DynamicParameters p = new DynamicParameters();
p.Add("@EventGuid", eventId.ToString());
connection.Open();
var reader = connection.QueryMultiple(sql, p);
var @event = reader.Read().Cast <IDictionary <string, object> >().Select <dynamic, Event>(
row => new Event(Guid.Parse(row.ClubGuid.ToString()), Guid.Parse(row.EventGuid.ToString()))
{
EndDate = row.EndDate, Location = row.Location, StartDate = row.StartDate, Title = row.Title,
EventType = (EventType)row.EventTypeId
}).FirstOrDefault();
if (@event != null)
{
@event.Squads = reader.Read().Cast <IDictionary <string, object> >().Select <dynamic, Squad>(
row => new Squad(@event.ClubId, Guid.Parse(row.SquadGuid.ToString()))
{
Name = row.Name
}).ToList();
}
if (@event != null)
{
@event.TrainingMaterials = reader.Read().Cast <IDictionary <string, object> >().Select <dynamic, TrainingMaterial>(
row => new TrainingMaterial(@event.ClubId, Guid.Parse(row.TrainingMaterialGuid.ToString()))
{
Title = row.Title,
ContentType = row.ContentType,
Description = row.Description,
ExternalId = row.ExternalId,
ThumbnailUrl = row.ThumbnailUrl,
Url = row.Url
}).ToList();
}
return(@event);
}
}
public AssignmentDetails GetAssignment(Guid clubId, Guid assignmentId)
{
var sql = $@"SELECT A.DueDate,
A.Guid AS AssignmentGuid,
CAST(A.CreatedOn AS DATE) AS CreatedOn,
A.Instructions,
A.Title, Cl.Guid AS ClubGuid,
Co.FirstName + ' ' + Co.LastName AS CreatedBy,
STUFF ((SELECT DISTINCT COALESCE(S1.Name + ', ', '') FROM PlayerAssignments PA1
INNER JOIN Players P1 ON PA1.PlayerId = P1.PlayerId AND (P1.Deleted IS NULL OR P1.Deleted = 0)
INNER JOIN Squads S1 ON P1.SquadId = S1.SquadId
WHERE A.AssignmentId = PA1.AssignmentId
FOR XML PATH(''),TYPE ).value('.','VARCHAR(50)')
,1, 0, '') AS Squads
FROM Assignments A
INNER JOIN Clubs Cl ON A.ClubId = Cl.ClubId
INNER JOIN Coaches Co ON Cl.ClubId = Co.ClubId AND Co.CoachId = A.CoachId
WHERE Cl.Guid = @ClubGuid AND A.Guid = @AssignmentGuid
GROUP BY A.AssignmentId, A.DueDate, A.Guid, A.CreatedOn, A.Instructions, A.Title, Cl.Guid, Co.FirstName, Co.LastName;
SELECT T.Guid AS TrainingMaterialGuid
,T.Title
,T.Description
,T.Url
,T.ThumbnailUrl
,T.ContentType
FROM AssignmentTrainingMaterials ATM
INNER JOIN Assignments A ON A.AssignmentId = ATM.AssignmentId
INNER JOIN TrainingMaterials T ON ATM.TrainingMaterialId = T.TrainingMaterialId
INNER JOIN Clubs C ON C.ClubId = A.ClubId
WHERE C.Guid = @ClubGuid AND A.Guid = @AssignmentGuid AND (T.Deleted IS NULL OR T.Deleted = 0);
SELECT P.Guid AS PlayerGuid, P.FirstName + ' ' + P.LastName + ' (' + S.Name + ')' AS PlayerName
FROM PlayerAssignments PA
INNER JOIN Assignments A ON PA.AssignmentId = A.AssignmentId
INNER JOIN Players P ON P.PlayerId = PA.PlayerId AND (P.Deleted IS NULL OR P.Deleted = 0)
INNER JOIN Squads S ON P.SquadId = S.SquadId
INNER JOIN Clubs C ON C.ClubId = A.ClubId AND S.ClubId = C.ClubId
WHERE A.Guid = @AssignmentGuid AND C.Guid = @ClubGuid AND (P.Deleted IS NULL OR P.Deleted = 0)
ORDER BY S.Name, PlayerName;";
DynamicParameters p = new DynamicParameters();
p.Add("@ClubGuid", clubId.ToString());
p.Add("@AssignmentGuid", assignmentId.ToString());
using (var connection = connectionFactory.Connect()) {
connection.Open();
var reader = connection.QueryMultiple(sql, p);
var assignment = reader.Read().Cast <IDictionary <string, dynamic> >().Select <dynamic, AssignmentDetails>(
row => new AssignmentDetails(Guid.Parse(row.ClubGuid.ToString()), Guid.Parse(row.AssignmentGuid.ToString()))
{
CreatedBy = row.CreatedBy,
CreatedOn = row.CreatedOn.ToString("dd-MMM-yyyy"),
Instructions = row.Instructions,
Title = row.Title,
DueDate = row.DueDate,
FormattedDueDate = row.DueDate.ToString("dd-MMM-yyyy"),
Squads = ((row.Squads?.Trim().EndsWith(",") ?? false) ? row.Squads.TrimEnd(new char[] { ',', ' ' }) : row.Squads)
}).SingleOrDefault();
if (assignment != null)
{
assignment.TrainingMaterials = reader.Read().Cast <IDictionary <string, dynamic> >().Select <dynamic, MiniTrainingMaterial>(
row => new MiniTrainingMaterial(row.TrainingMaterialGuid, row.Title)
{
Description = row.Description, ThumbnailUrl = row.ThumbnailUrl, Url = row.Url, ContentType = row.ContentType
}).ToList();
assignment.Players = reader.Read().Cast <IDictionary <string, dynamic> >().Select <dynamic, MiniModel>(
row => new MiniModel(row.PlayerGuid, row.PlayerName)
{
}).ToList();
}
return(assignment);
}
}
public async Task <T> FindById(IdType id)
{
using IDbConnection conn = _dbConnectionFactory.Connect();
return(await conn.GetAsync <T>(id));
}
