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