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