public async Task <IReadOnlyCollection <QuizOverviewViewModel> > GetQuizzesByTagsAsync(string tag) { Debug.Assert(!String.IsNullOrEmpty(tag)); string sql = @" select q.Id as Id, q.Name as Name, q.Description as Description, qqc.QuestionPointAverage as AveragePoints, 0 as IsComplete, q.ImageFileName as ThumbnailImagePath, qc.Id as QuizCategoryId, qc.Name as QuizCategoryName from dbo.Quizs q inner join ( select qq.Quiz_Id as QuizId, round(avg(cast(qu.Points as float)), 0) as QuestionPointAverage from dbo.QuestionQuizs qq inner join dbo.questions qu on qu.Id = qq.Question_Id group by qq.Quiz_Id ) qqc on qqc.QuizId = q.Id inner join dbo.QuestionQuizs as qq on qq.Quiz_Id = q.Id inner join dbo.questions qu on qu.Id = qq.Question_Id inner join dbo.TagQuestions tq on tq.Question_Id = qu.Id inner join dbo.Tags t on t.TagId = tq.Tag_TagId inner join dbo.QuizCategories qc on qc.Id = q.QuizCategoryId where t.Name in (@tagList) group by q.Id, q.Name, q.Description, q.ImageFileName, qqc.QuestionPointAverage, qc.Id, qc.Name "; var results = await QueryAsync <QuizOverviewViewModel>(sql, new { tagList = tag }); foreach (var result in results) { result.ThumbnailImagePath = QuizExtensions.GetThumbnailImagePath(result.ThumbnailImagePath); } return(results.ToList().AsReadOnly()); }
public async Task <IReadOnlyCollection <CompletedQuizFeedViewModel> > GetFollowerQuizCompletionsAsync(string userId) { string sql = @" select p.Id SourceProfileId, u.UserName SourceUserName, ui.FileName SourceProfileImagePath, q.Name QuizName, q.Id QuizId, q.ImageFileName as QuizThumbnailImagePath, cq.DateCompleted DateCompleted, ( case when q.QuizTypeId = @quizTypeIndividual then ( select count(*) from dbo.QuestionQuizs qq2 inner join dbo.AnsweredQuestions aq on aq.UserId = cq.UserId and aq.QuestionId = qq2.Question_Id inner join dbo.Questions qu on qu.Id = aq.QuestionId where qq2.Quiz_Id = q.Id and aq.AnswerId = qu.CorrectAnswerId ) else ( select count(*) from dbo.answeredminefieldquestions amq inner join dbo.MinefieldAnswers ma on ma.Id = amq.MinefieldAnswerId inner join dbo.MinefieldQuestions mq on mq.MinefieldQuestionId = amq.MinefieldQuestionId where mq.MinefieldQuestionId = q.Id and ma.IsCorrect = 1 and amq.UserId = u.id ) end ) CorrectAnswerCount, ( case when q.QuizTypeId = @quizTypeIndividual then ( select count(*) from dbo.QuestionQuizs qq2 where qq2.Quiz_Id = q.Id ) else ( select count(*) from dbo.MinefieldAnswers ma where MinefieldQuestionId = q.Id and ma.IsCorrect = 1 ) end ) TotalAnswerCount from dbo.CompletedQuizs cq inner join dbo.Quizs q on cq.QuizId = q.Id inner join dbo.AspNetUsers u on cq.UserId = u.Id inner join dbo.Profiles p on p.ApplicationUser_Id = u.Id left join dbo.UserImages ui on p.UserImageId = ui.Id inner join dbo.FavoriteProfiles fp on p.Id = fp.ProfileId where fp.UserId = @userId and u.IsActive = 1" ; var results = await QueryAsync <CompletedQuizFeedViewModel>(sql, new { userId = userId, quizTypeIndividual = (int)QuizTypeValues.Individual }); foreach (var result in results) { result.SourceProfileImagePath = ProfileExtensions.GetThumbnailImagePath(result.SourceProfileImagePath); result.QuizThumbnailImagePath = QuizExtensions.GetThumbnailImagePath(result.QuizThumbnailImagePath); } return(results.ToList().AsReadOnly()); }