public async Task <IEnumerable <ChatUserDto> > GetAsync(CancellationToken token)
{
var userId = _userManager.GetLongUserId(User);
var result = await _queryBus.QueryAsync(new ChatConversationsQuery(userId), token);
result = result.Select(s =>
{
s.Image = _urlBuilder.BuildUserImageEndpoint(s.UserId, s.Image);
return(s);
});
return(result);
}
public async Task <IEnumerable <QuestionFeedDto> > GetQuestionsAsync(long id, int page, CancellationToken token)
{
var query = new UserQuestionsByIdQuery(id, page);
var res = await _queryBus.QueryAsync(query, token);
return(res.Select(item =>
{
item.User.Image = _urlBuilder.BuildUserImageEndpoint(item.User.Id, item.User.Image);
return item;
}));
}
public async Task <IEnumerable <TutorCardDto> > GetTopTutorsAsync(int count,
[ProfileModelBinder(ProfileServiceQuery.Country)] UserProfile profile,
CancellationToken token)
{
var query = new TopTutorsQuery(profile.Country, count);
var result = await _queryBus.QueryAsync(query, token);
return(result.Select(s =>
{
s.Image = _urlBuilder.BuildUserImageEndpoint(s.UserId, s.Image);
return s;
}));
}
public async Task <WebResponseWithFacet <TutorCardDto> > GetAsync(
string term, string course,
[ProfileModelBinder(ProfileServiceQuery.Country)] UserProfile profile,
int page,
[FromServices] ITutorSearch tutorSearch,
CancellationToken token, int pageSize = 20)
{
term = term ?? string.Empty;
course = course ?? string.Empty;
term = $"{term} {course}".Trim();
//TODO make it better
if (string.IsNullOrWhiteSpace(term))
{
_userManager.TryGetLongUserId(User, out var userId);
var query = new TutorListQuery(userId, profile.Country, page, pageSize);
var result = await _queryBus.QueryAsync(query, token);
result.Result = result.Result.Select(s =>
{
s.Image = _urlBuilder.BuildUserImageEndpoint(s.UserId, s.Image);
return(s);
});
return(new WebResponseWithFacet <TutorCardDto>
{
Result = result.Result,
Count = result.Count,
NextPageLink = Url.RouteUrl("TutorSearch", new { page = ++page })
});
}
else
{
var query = new TutorListTabSearchQuery(term, profile.Country, page, pageSize);
var result = await tutorSearch.SearchAsync(query, token);
//result.Result = result.Result.Select(s =>
//{
// s.Image = _urlBuilder.BuildUserImageEndpoint(s.UserId, s.Image);
// return s;
//});
return(new WebResponseWithFacet <TutorCardDto>
{
Result = result.Result,
NextPageLink = Url.RouteUrl("TutorSearch", new { page = ++page, term }),
Count = result.Count
});
}
}
public async Task <ActionResult <StudyRoomDto> > GetStudyRoomAsync(Guid id,
[FromServices] IUrlBuilder urlBuilder, CancellationToken token)
{
var userId = _userManager.GetLongUserId(User);
var query = new StudyRoomQuery(id, userId);
var result = await _queryBus.QueryAsync(query, token);
//TODO: need to add who is the tutor
if (result == null)
{
return(NotFound());
}
result.StudentImage = urlBuilder.BuildUserImageEndpoint(result.StudentId, result.StudentImage);
result.TutorImage = urlBuilder.BuildUserImageEndpoint(result.TutorId, result.TutorImage);
return(result);
}
public async Task <ActionResult <DocumentPreviewResponse> > GetAsync(long id,
[FromServices] IQueueProvider queueProvider,
[FromServices] ICrawlerResolver crawlerResolver,
[FromServices] IIndex <DocumentType, IDocumentGenerator> generatorIndex,
[FromServices] IUrlBuilder urlBuilder,
CancellationToken token)
{
long?userId = null;
if (User.Identity.IsAuthenticated)
{
userId = _userManager.GetLongUserId(User);
}
var query = new DocumentById(id, userId);
var model = await _queryBus.QueryAsync(query, token);
if (model == null)
{
return(NotFound());
}
model.Document.User.Image = urlBuilder.BuildUserImageEndpoint(model.Document.User.Id, model.Document.User.Image);
if (model.Tutor != null)
{
model.Tutor.Image =
urlBuilder.BuildUserImageEndpoint(model.Tutor.UserId, model.Tutor.Image);
}
var tQueue = queueProvider.InsertMessageAsync(new UpdateDocumentNumberOfViews(id), token);
var textTask = Task;
if (crawlerResolver.Crawler != null)
{
textTask = _blobProvider.DownloadTextAsync("text.txt", query.Id.ToString(), token);
}
var files = await generatorIndex[model.Document.DocumentType].GeneratePreviewAsync(model, userId.GetValueOrDefault(-1), token);
await System.Threading.Tasks.Task.WhenAll(tQueue, textTask);
model.Document.Url = Url.DocumentUrl(model.Document.Course, model.Document.Id, model.Document.Title);
return(new DocumentPreviewResponse(model, files, textTask.Result));
}
public async Task <ActionResult <QuestionDetailDto> > GetQuestionAsync(long id,
[FromServices] IQueryBus bus,
[FromServices] IUrlBuilder urlBuilder, CancellationToken token)
{
var query = new QuestionDataByIdQuery(id);
var retVal = await bus.QueryAsync(query, token);
if (retVal == null)
{
return(NotFound());
}
retVal.User.Image = urlBuilder.BuildUserImageEndpoint(retVal.User.Id, retVal.User.Image);
foreach (var answer in retVal.Answers)
{
answer.User.Image = urlBuilder.BuildUserImageEndpoint(answer.User.Id, answer.User.Image);
}
return(retVal);
}
public async Task <IEnumerable <UserPurchaseDto> > GetAsync(UserPurchasesByIdQuery query, CancellationToken token)
{
//TODO: need to return Document with state = archive
var documentFuture = _session.Query <DocumentTransaction>()
.Fetch(f => f.User)
.Fetch(f => f.Document)
.Where(w => w.User.Id == query.Id)
.Where(w => w.Document.Status.State == Core.Enum.ItemState.Ok)
.Where(w => w.Type == Core.Enum.TransactionType.Spent)
.Select(s => new PurchasedDocumentDto()
{
Id = s.Document.Id,
Name = s.Document.Name,
Course = s.Document.Course.Id,
Type = s.Document.DocumentType != null ?
(ContentType)s.Document.DocumentType :
ContentType.Document,
Date = s.Created,
Price = s.Price
}).ToFuture <UserPurchaseDto>();
//TODO: Please check if the ended and price happens in the same place
var sessionFuture = _session.Query <StudyRoomSession>()
.Fetch(f => f.StudyRoom)
.ThenFetch(f => f.Users)
.Where(w => w.StudyRoom.Users.Select(s => s.User.Id).Any(a => a == query.Id) && query.Id != w.StudyRoom.Tutor.Id)
.Where(w => w.Ended != null)
.Select(s => new PurchasedSessionDto()
{
Date = s.Created,
Price = s.Price.GetValueOrDefault(),
Duration = s.Duration,
TutorName = s.StudyRoom.Tutor.User.Name,
TutorId = s.StudyRoom.Tutor.Id,
TutorImage = _urlBuilder.BuildUserImageEndpoint(s.StudyRoom.Tutor.Id, s.StudyRoom.Tutor.User.ImageName, s.StudyRoom.Tutor.User.Name, null)
}).ToFuture <UserPurchaseDto>();
var buyPointsFuture = _session.Query <BuyPointsTransaction>()
.Fetch(s => s.User)
.Where(w => w.User.Id == query.Id)
.Select(s => new PurchasedBuyPointsDto()
{
Id = s.Id,
Price = s.Price,
Country = s.User.Country,
Date = s.Created
}).ToFuture <UserPurchaseDto>();
var documentResult = await documentFuture.GetEnumerableAsync(token);
var sessionResult = await sessionFuture.GetEnumerableAsync(token);
var buyPointsResult = await buyPointsFuture.GetEnumerableAsync(token);
return(documentResult.Union(sessionResult).Union(buyPointsResult).OrderByDescending(o => o.Date));
}
private ChatMessageDto BuildChatMessage(ChatTextMessage chatMessage)
{
return(new ChatTextMessageDto
{
UserId = chatMessage.User.Id,
Text = chatMessage.Message,
DateTime = DateTime.UtcNow,
Name = chatMessage.User.Name,
Image = _urlBuilder.BuildUserImageEndpoint(chatMessage.User.Id, chatMessage.User.ImageName)
});
}
public async Task <IEnumerable <PendingTutorsDto> > GetPendingTutorsAsync([FromServices] IUrlBuilder urlBuilder, CancellationToken token)
{
var query = new PendingTutorsQuery(User.GetCountryClaim());
var res = await _queryBus.QueryAsync(query, token);
return(res.Select(item =>
{
item.Image = urlBuilder.BuildUserImageEndpoint(item.Id, item.Image);
return item;
}));
}
//[Authorize(Policy = Policy.IsraelUser)]
//[Authorize(Policy = Policy.GlobalUser)]
// [ResponseCache(Location = ResponseCacheLocation.Client, Duration = TimeConst.Hour, VaryByQueryKeys = new []{ "*" })]
public async Task <IEnumerable <ChatMessageDto> > Get(string identifier,
[FromServices] IUrlBuilder urlBuilder,
CancellationToken token)
{
var result = await _queryBus.QueryAsync(new ChatConversationByIdQuery(identifier, 0, User.GetCountryClaim()), token);
return(result.Select(item =>
{
item.Image = urlBuilder.BuildUserImageEndpoint(item.UserId, item.Image);
return item;
}));
}
public async Task <IEnumerable <UserStudyRoomDto> > GetUserLobbyStudyRooms([FromServices] IUrlBuilder urlBuilder, CancellationToken token)
{
var userId = _userManager.GetLongUserId(User);
var query = new UserStudyRoomQuery(userId);
var res = await _queryBus.QueryAsync(query, token);
return(res.Select(item =>
{
item.Image = urlBuilder.BuildUserImageEndpoint(item.UserId, item.Image);
return item;
}));
}
public async Task <IEnumerable <FollowersDto> > GetFollowersAsync([FromServices] IUrlBuilder urlBuilder,
CancellationToken token)
{
var userId = _userManager.GetLongUserId(User);
var query = new UserFollowersByIdQuery(userId);
var result = await _queryBus.QueryAsync(query, token);
return(result.Select(s =>
{
s.Image = urlBuilder.BuildUserImageEndpoint(s.UserId, s.Image, s.Name);
return s;
}));
}
//[Authorize(Policy = Policy.IsraelUser)]
public async Task <IEnumerable <ConversationDetailsDto> > ConversationDetailAsync(
[FromRoute] string identifier,
[FromServices] IUrlBuilder urlBuilder,
CancellationToken token)
{
var query = new ConversationDetailsQuery(identifier, User.GetCountryClaim());
var res = await _queryBus.QueryAsync(query, token);
return(res.Select(item =>
{
item.Image = urlBuilder.BuildUserImageEndpoint(item.UserId, item.Image);
return item;
}));
}
public async Task <ListWithCountDto <TutorCardDto> > GetAsync(TutorListQuery query, CancellationToken token)
{
const string sql = @"Select rt.Id as UserId,
rt.Name as 'Name', rt.ImageName as 'Image', rt.Courses, rt.Subjects, rt.Price,
rt.Rate, rt.RateCount as ReviewsCount, rt.Bio, rt.University, rt.Lessons, rt.Country, rt.SubsidizedPrice as DiscountPrice
from sb.ReadTutor rt
where rt.Country = coalesce(@country, (select country from sb.[user] where Id = @userId))
and rt.Id != @userId
order by
CASE
WHEN exists (
select uc.courseId from sb.UsersCourses uc where rt.Id = uc.UserId and uc.CanTeach = 1
INTERSECT
select uc2.CourseId from sb.UsersCourses uc2 where uc2.UserId = @userid) THEN 2
WHEN exists (
select c.subjectId from sb.UsersCourses uc join sb.Course c on uc.CourseId = c.Name
where rt.Id = uc.UserId and uc.CanTeach = 1
intersect
select c2.SubjectId from sb.UsersCourses uc2 join sb.Course c2 on uc2.CourseId = c2.Name
where uc2.UserId = @userid
) THEN 1
else 0
end desc, rt.Rating desc
OFFSET @PageSize * (@PageNumber) ROWS
FETCH NEXT @PageSize ROWS ONLY;
Select count(distinct rt.Id)
from sb.ReadTutor rt
where rt.Country = coalesce(@country, (select country from sb.[user] where Id = @userId))
and rt.Id != @userid;";
using (var conn = _dapper.OpenConnection())
using (var multi = conn.QueryMultiple(sql, new { query.UserId, query.Country, query.PageSize, @PageNumber = query.Page }))
{
var tutor = await multi.ReadAsync <TutorCardDto>();
var count = await multi.ReadFirstAsync <int>();
return(new ListWithCountDto <TutorCardDto>()
{
Count = count,
Result = tutor.Select(s =>
{
s.Image = _urlBuilder.BuildUserImageEndpoint(s.UserId, s.Image);
return s;
})
});
}
}
public async Task <ListWithCountDto <TutorCardDto> > SearchAsync(TutorListTabSearchQuery query, CancellationToken token)
{
//const int pageSize = 25;
var searchParams = new SearchParameters()
{
Top = query.PageSize,
Skip = query.Page * query.PageSize,
Select = new[]
{
nameof(Entities.Tutor.Data),
},
HighlightFields = new[] { nameof(Entities.Tutor.Courses) },
HighlightPostTag = string.Empty,
HighlightPreTag = string.Empty,
SearchFields = new[] { nameof(Entities.Tutor.Name),
nameof(Entities.Tutor.Prefix),
nameof(Entities.Tutor.Courses),
nameof(Entities.Tutor.Subjects) },
ScoringProfile = TutorSearchWrite.ScoringProfile,
IncludeTotalResultCount = true
};
if (!string.IsNullOrEmpty(query.Country))
{
searchParams.Filter = $"{nameof(Entities.Tutor.Country)} eq '{query.Country.ToUpperInvariant()}'";
}
var result = await _client.Documents.SearchAsync <Entities.Tutor>(query.Term, searchParams, cancellationToken : token);
var obj = new ListWithCountDto <TutorCardDto>()
{
Result = result.Results.Where(w => w.Document.Data != null).Select(s =>
{
var tutor = s.Document.Data;
var courses = (s.Highlights?[nameof(Entities.Tutor.Courses)] ?? Enumerable.Empty <string>()).Union(
s.Document.Data.Courses).Take(3).Distinct(StringComparer.OrdinalIgnoreCase);
if (tutor.Image != null)
{
s.Document.Data.Image = _urlBuilder.BuildUserImageEndpoint(tutor.UserId, tutor.Image);
}
s.Document.Data.Courses = courses;
s.Document.Data.Subjects = s.Document.Data.Subjects?.Take(3);
return(s.Document.Data);
}),
Count = result.Count
};
return(obj);
}
public async Task <IEnumerable <DocumentFeedDto> > GetAsync(SimilarDocumentsQuery query, CancellationToken token)
{
const string sql = @"with cte as
(
select d.UniversityId, CourseName, u.Country
from sb.Document d
join sb.[user] u
on d.UserId = u.Id
where d.Id = :Id
)
select top 10 d.Id, d.UpdateTime as DateTime, d.CourseName as Course, d.Name as Title,
un.Name as University,
coalesce(d.Description, d.MetaContent) as Snippet, d.Views, d.Downloads, d.Price,
d.DocumentType, d.Duration,
(
select count(1)
from sb.[Transaction] t
where t.TransactionType='Document' and
t.DocumentId=d.Id
and t.[Action]='SoldDocument'
) as Purchased,
d.VoteCount as [Vote.Votes],
u.Id as [User.Id], u.Name as [User.Name],
u.ImageName as [User.Image]
from sb.[Document] d
inner join sb.[User] u on d.UserId=u.Id
left outer join sb.[University] un on d.UniversityId=un.Id,
cte
where d.CourseName = cte.CourseName and u.Country = cte.Country
and d.Id != :Id and d.[State]='ok'
order by case when d.UniversityId=cte.UniversityId
then 1 else 0 end desc, d.DocumentType desc, d.UpdateTime desc;
";
var res = await _session.CreateSQLQuery(sql)
.SetInt64("Id", query.DocumentId)
.SetResultTransformer(new DeepTransformer <DocumentFeedDto>('.', new SbAliasToBeanResultTransformer <DocumentFeedDto>()))
.ListAsync <DocumentFeedDto>(token);
return(res.Select(s =>
{
s.User.Image = _urlBuilder.BuildUserImageEndpoint(s.User.Id, s.User.Image);
s.Preview = _urlBuilder.BuildDocumentThumbnailEndpoint(s.Id);
return s;
}));
}
public IEnumerable <SitemapNode> GetUrls(bool isFrymo, int index)
{
var t = _session.Query <ReadTutor>();
if (isFrymo)
{
t = t.Where(w => w.Country == Country.India.Name);
}
else
{
t = t.Where(w => w.Country != Country.India.Name);
}
var tutors = t.Take(SiteMapController.PageSize)
.Skip(SiteMapController.PageSize * index)
.Select(s => new { s.Id, s.Name, s.ImageName });
foreach (var item in tutors)
{
var url = _linkGenerator.GetUriByRouteValues(_httpContextAccessor.HttpContext, SeoTypeString.Tutor, new
{
item.Id,
item.Name
});
yield return(new SitemapNode(url)
{
ChangeFrequency = ChangeFrequency.Daily,
Priority = 1,
TimeStamp = DateTime.UtcNow,
Images = new List <SitemapImage>()
{
new SitemapImage(_urlBuilder.BuildUserImageEndpoint(item.Id, item.ImageName, item.Name, new
{
width = "214",
height = "240"
}))
{
Caption = $"{item.Name} profile image"
}
}
});
}
}
public async Task <IEnumerable <AccountQuestionDto> > GetAsync(AccountQuestionsQuery query, CancellationToken token)
{
const string sql = @"with cte as (
select top 1 * from (select 1 as o, u2.Id as UniversityId, COALESCE(u2.country,u.country) as Country, u.id as userid
from sb.[user] u
left join sb.University u2 on u.UniversityId2 = u2.Id
where u.id = :userid
union
select 2,null,:country,0) t
order by o
)
select top 50 q.Id,
q.Text,
q.Updated as [DateTime],
u.Id as [User.Id],
u.Name as [User.Name],
u.ImageName as [User.Image]
from sb.Question q
join sb.[user] u
on u.Id = q.UserId
,cte
where not exists (select Id from sb.Answer where QuestionId = q.Id and State = 'Ok' and UserId = :userid)
and q.Updated > GETUTCDATE() - 182
and q.State = 'Ok'
and q.userId != :userid
and cte.country = u.country
order by
case when q.CourseId in (select courseId from sb.usersCourses where userid = cte.userid) then 4 else 0 end +
case when q.UniversityId = cte.UniversityId then 3 else 0 end +
cast(1 as float)/ISNULL(nullif( DATEDIFF(minute, q.Updated, GETUTCDATE() ),0),1) desc";
var res = await _session.CreateSQLQuery(sql)
.SetInt64("userid", query.Id)
.SetString("country", query.Country)
.SetResultTransformer(new DeepTransformer <AccountQuestionDto>('.', new SbAliasToBeanResultTransformer <AccountQuestionDto>()))
.ListAsync <AccountQuestionDto>(token);
return(res.Select(s =>
{
s.User.Image = _urlBuilder.BuildUserImageEndpoint(s.User.Id, s.User.Image);
return s;
}));
}
public async Task<IEnumerable<SaleDto>> GetUserSalesAsync([FromServices] IUrlBuilder urlBuilder, CancellationToken token)
{
var userId = _userManager.GetLongUserId(User);
var query = new UserSalesByIdQuery(userId);
var result = await _queryBus.QueryAsync(query, token);
return result.Select(s =>
{
if (s is DocumentSaleDto d)
{
d.Preview = urlBuilder.BuildDocumentThumbnailEndpoint(d.Id);
d.Url = Url.DocumentUrl(d.Course, d.Id, d.Name);
}
if (s is SessionSaleDto ss)
{
ss.StudentImage = urlBuilder.BuildUserImageEndpoint(ss.StudentId, ss.StudentImage, ss.StudentName);
}
return s;
});
}
public async Task <ActionResult <UserAccountDto> > GetAsync(
[FromServices] IQueryBus queryBus,
[FromServices] ILogger logger,
[FromServices] IUrlBuilder urlBuilder,
CancellationToken token)
{
var userId = _userManager.GetLongUserId(User);
var query = new UserAccountQuery(userId);
var user = await queryBus.QueryAsync(query, token);
if (user == null)
{
await _signInManager.SignOutAsync();
logger.Error($"User is null {userId}");
return(Unauthorized());
}
user.Image = urlBuilder.BuildUserImageEndpoint(userId, user.Image);
return(user);
}
public async Task <UserProfileDto> GetAsync(UserProfileQuery query, CancellationToken token)
{
const string sql = @"select u.id,
u.ImageName as Image,
u.Name,
u2.name as universityName,
u.description,
u.online,
cast ((select count(*) from sb.GoogleTokens gt where u.Id = gt.Id) as bit) as CalendarShared,
u.FirstName as FirstName,
u.LastName as LastName,
t.price as Tutor_Price,
t.SubsidizedPrice as Tutor_DiscountPrice,
t.country as Tutor_TutorCountry,
t.rate as Tutor_Rate,
t.rateCount as Tutor_ReviewCount,
t.Bio as Tutor_Bio,
t.Lessons as Tutor_Lessons,
(select count(*) from sb.document d where d.userid = u.id and d.state = 'Ok')
+ (select count(*) from sb.Question d where d.userid = u.id and d.state = 'Ok')
as Tutor_ContentCount,
(select count(distinct UserId) from sb.StudyRoom sr
join sb.StudyRoomUser sru on sr.Id = sru.StudyRoomId
where sr.TutorId = :profileId and sru.UserId != :profileId) as Tutor_Students,
(select count(1) from sb.UsersRelationship where UserId = u.Id) as Followers,
case when exists (select * from sb.UsersRelationship ur where ur.UserId = :profileId and ur.FollowerId = :userid) then cast(1 as bit) else cast(0 as bit) end as IsFollowing
from sb.[user] u
left join sb.[University] u2 on u.UniversityId2 = u2.Id
left join sb.readTutor t
on U.Id = t.Id
where u.id = :profileId
and (u.LockoutEnd is null or u.LockoutEnd < GetUtcDate())";
const string couponSql = @"Select c.value as Value,
c.CouponType as Type
from sb.UserCoupon uc
join sb.coupon c on uc.couponId = c.id and uc.UsedAmount < c.AmountOfUsePerUser
where userid = :userid
and uc.tutorId = :profileId";
var sqlQuery = _session.CreateSQLQuery(sql);
sqlQuery.SetInt64("profileId", query.Id);
sqlQuery.SetInt64("userid", query.UserId);
sqlQuery.SetResultTransformer(new DeepTransformer <UserProfileDto>('_'));
var profileValue = sqlQuery.FutureValue <UserProfileDto>();
var couponSqlQuery = _session.CreateSQLQuery(couponSql);
couponSqlQuery.SetInt64("profileId", query.Id);
couponSqlQuery.SetInt64("userid", query.UserId);
couponSqlQuery.SetResultTransformer(Transformers.AliasToBean <CouponDto>());
var couponValue = couponSqlQuery.FutureValue <CouponDto>();
var future = _session.Query <ReadTutor>().Where(t => t.Id == query.Id)
.Select(s => s.Subjects).ToFutureValue();
var coursesFuture = _session.Query <Document>()
.Fetch(f => f.User)
.Where(w => w.User.Id == query.Id && w.Status.State == Core.Enum.ItemState.Ok)
.Select(s => s.Course.Id).Distinct()
.ToFuture();
var result = await profileValue.GetValueAsync(token);
var couponResult = couponValue.Value;
var coursesResult = await coursesFuture.GetEnumerableAsync(token);
if (result is null)
{
return(null);
}
if (result.Tutor != null)
{
result.Tutor.Subjects = future.Value;
if (couponResult != null)
{
result.Tutor.CouponType = couponResult.TypeEnum;
result.Tutor.CouponValue = couponResult.Value;
}
}
result.Courses = coursesResult;
result.Image = _urlBuilder.BuildUserImageEndpoint(result.Id, result.Image);
if (result.Tutor?.CouponValue != null && result.Tutor?.CouponType != null)
{
result.Tutor.HasCoupon = true;
result.Tutor.DiscountPrice = Coupon.CalculatePrice(result.Tutor.CouponType.Value, result.Tutor.Price,
result.Tutor.CouponValue.Value);
}
return(result);
}
// If you chnage enything in the sql query tou need to take care to
// QuestionFeedWithFliterQuery and DocumentFeedWithFilterQuery as well
public async Task <IEnumerable <FeedDto> > GetAsync(FeedAggregateQuery query, CancellationToken token)
{
const string sqlWithCourse = @"with cte as (
select top 1 * from(select 1 as o, u2.Id as UniversityId, COALESCE(u2.country, u.country) as Country, u.id as userid
from sb.[user] u
left
join sb.University u2 on u.UniversityId2 = u2.Id
where u.id = @userid
union
select 2, null, @country, 0) t
order by o
)
select R.*
from
(
select 'd' as type
, d.CourseName as Course
, d.UniversityId as UniversityId
, d.UpdateTime as DateTime
, (select d.Id--id,
, d.Price
, d.CourseName as Course
, d.UpdateTime as DateTime
, d.Language as CultureInfo
, un.Name as University
, u.Id as 'User.Id'
, u.Name as 'User.Name'
, u.ImageName as 'User.Image'
, un.Id as UniversityId
, COALESCE(d.description,metaContent) as Snippet
, d.Name as Title
, d.[Views]
, d.Downloads
, d.VoteCount as 'Vote.Votes'
, (select v.VoteType from sb.Vote v where v.DocumentId = d.Id and v.UserId = cte.userid) as 'Vote.Vote'
,(select count(1) from sb.[Transaction] where DocumentId = d.Id and [Action] = 'SoldDocument') as Purchased
,d.duration as Duration
,d.DocumentType as documentType for json path) as JsonArray,
case when d.DocumentType = 'Video' then 1 else 0 end as IsVideo,
case when (select UserId from sb.UsersRelationship ur where ur.FollowerId = @userId and u.Id = ur.UserId) = u.id then 1 else 0 end as IsFollow
from sb.document d
join sb.[user] u on d.UserId = u.Id
left join sb.University un on un.Id = d.UniversityId
join cte on un.country = cte.country or u.country = cte.country
where
d.State = 'Ok'
and d.courseName = @course
union all
SELECT 'q' as type
,q.CourseId as Course
,q.UniversityId as UniversityId
,q.Updated as DateTime
,(select q.Id as Id,
q.Text as Text,
q.CourseId as Course,
(SELECT count(*) as y0_ FROM sb.[Answer] this_0_ WHERE(this_0_.QuestionId = q.Id and this_0_.State = 'Ok')) as Answers,
q.Updated as DateTime,
q.Language as CultureInfo
--TODO from cross join
,x.Id as 'FirstAnswer.User.Id'
,x.ImageName as 'FirstAnswer.User.Image'
,x.Name as 'FirstAnswer.User.Name'
,x.Text as 'FirstAnswer.Text'
,x.Created as 'FirstAnswer.DateTime'
,u.Id as 'User.Id'
,u.Name as 'User.Name'
,u.ImageName as 'User.Image'
,'Question' as documentType for json path) JsonArray,
0 as IsVideo,
case when (select UserId from sb.UsersRelationship ur where ur.FollowerId = @userId and u.Id = ur.UserId) = u.id then 1 else 0 end as IsFollow
FROM sb.[Question] q
join sb.[user] u
on q.UserId = u.Id
outer apply (
select top 1 text, u.id, u.name, u.ImageName, a.Created from sb.Answer a join sb.[user] u on a.userid = u.id
where a.QuestionId = q.Id and state = 'Ok' order by a.created
) as x
,cte
where
u.country = cte.country
and q.courseId = @course
and q.State = 'Ok'
) R,
cte
order by
case when R.UniversityId = cte.UniversityId or R.UniversityId is null then 0 else DATEDiff(hour, GetUtcDATE() - 180, GetUtcDATE()) end +
DATEDiff(hour, R.DateTime, GetUtcDATE()) +
case when r.IsVideo = 1 then 0 else DATEDiff(hour, GetUtcDATE() - 7, GetUtcDATE()) end +
case when r.IsFollow = 1 then 0 else DATEDiff(hour, GetUtcDATE() - 7, GetUtcDATE()) end
OFFSET @page*@pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY";
const string sqlWithoutCourse = @"with cte as (
select top 1 * from (select 1 as o, u2.Id as UniversityId, COALESCE(u2.country,u.country) as Country, u.id as userid
from sb.[user] u
left join sb.University u2 on u.UniversityId2 = u2.Id
where u.id = @userid
union
select 2,null,@country,0) t
order by o
)
select R.*
from
(
select 'd' as type
,d.CourseName as Course
,d.UniversityId as UniversityId
,d.UpdateTime as DateTime
,(select d.Id --id,
,d.Price
,d.CourseName as Course
,d.UpdateTime as DateTime
,d.Language as CultureInfo
,un.Name as University
,u.Id as 'User.Id'
,u.Name as 'User.Name'
,u.ImageName as 'User.Image'
,un.Id as UniversityId
,COALESCE(d.description,metaContent) as Snippet
,d.Name as Title
,d.[Views]
,d.Downloads
,d.VoteCount as 'Vote.Votes'
,(select v.VoteType from sb.Vote v where v.DocumentId = d.Id and v.UserId = cte.userid) as 'Vote.Vote'
,(select count(1) from sb.[Transaction] where DocumentId = d.Id and [Action] = 'SoldDocument') as Purchased
,d.duration as Duration
,d.DocumentType as documentType for json path) as JsonArray,
case when d.DocumentType = 'Video' then 1 else 0 end as IsVideo,
case when (select UserId from sb.UsersRelationship ur where ur.FollowerId = @userId and u.Id = ur.UserId) = u.id then 1 else 0 end as IsFollow
from sb.document d
join sb.[user] u on d.UserId = u.Id
left join sb.University un on un.Id = d.UniversityId
join cte on un.country = cte.country or u.country = cte.country
where
d.UpdateTime > GETUTCDATE() - 182
and d.State = 'Ok'
and (d.CourseName in (select courseId from sb.usersCourses where userid = cte.userid) or @userid <= 0)
union all
SELECT 'q' as type
,q.CourseId as Course
,q.UniversityId as UniversityId
,q.Updated as DateTime
,(select q.Id as Id,
q.Text as Text,
q.CourseId as Course,
(SELECT count(*) as y0_ FROM sb.[Answer] this_0_ WHERE (this_0_.QuestionId = q.Id and this_0_.State = 'Ok')) as Answers,
q.Updated as DateTime,
q.Language as CultureInfo
--TODO from cross join
,x.Id as 'FirstAnswer.User.Id'
,x.ImageName as 'FirstAnswer.User.Image'
,x.Name as 'FirstAnswer.User.Name'
,x.Text as 'FirstAnswer.Text'
,x.Created as 'FirstAnswer.DateTime'
,u.Id as 'User.Id'
,u.Name as 'User.Name'
,u.ImageName as 'User.Image'
,'Question' as documentType for json path) JsonArray
, 0 as IsVideo,
case when (select UserId from sb.UsersRelationship ur where ur.FollowerId = @userId and u.Id = ur.UserId) = u.id then 1 else 0 end as IsFollow
FROM sb.[Question] q
join sb.[user] u
on q.UserId = u.Id
left join sb.University un on q.UniversityId = un.Id
outer apply (
select top 1 text,u.id,u.name,u.ImageName, a.Created from sb.Answer a join sb.[user] u on a.userid = u.id
where a.QuestionId = q.Id and state = 'Ok' order by a.created
) as x
join cte on un.country = cte.country or u.country = cte.country
where
q.Updated > GETUTCDATE() - 182
and q.State = 'Ok'
and (q.CourseId in (select courseId from sb.usersCourses where userid = cte.userid) or @userid <= 0)
) R,
cte
order by
case when R.Course in (select courseId from sb.usersCourses where userid = cte.userid) then 0 else DATEDiff(hour, GetUtcDATE() - 180, GetUtcDATE())*2 end +
case when R.UniversityId = cte.UniversityId or R.UniversityId is null then 0 else DATEDiff(hour, GetUtcDATE() - 180, GetUtcDATE()) end +
DATEDiff(hour, R.DateTime, GetUtcDATE()) +
case when r.IsVideo = 1 then 0 else DATEDiff(hour, GetUtcDATE() - 7, GetUtcDATE()) end +
case when r.IsFollow = 1 then 0 else DATEDiff(hour, GetUtcDATE() - 7, GetUtcDATE()) end
OFFSET @page*@pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY";
var sql = query.Course == null ? sqlWithoutCourse : sqlWithCourse;
//this is because we don't want to aggregate all the historical data
var result = new List <FeedDto>();
using (var conn = _dapperRepository.OpenConnection())
using (var reader = await conn.ExecuteReaderAsync(sql, new
{
query.Page,
query.UserId,
query.Country,
query.Course,
query.PageSize
}))
{
if (reader.Read())
{
var col = reader.GetOrdinal("type");
var colJson = reader.GetOrdinal("JsonArray");
do
{
var v = reader.GetString(colJson);
switch (reader.GetString(col))
{
case "q":
var question = _jsonSerializer.Deserialize <IEnumerable <QuestionFeedDto> >(v).First();
if (question.User.Image != null)
{
question.User.Image =
_urlBuilder.BuildUserImageEndpoint(question.User.Id, question.User.Image);
}
if (question.FirstAnswer?.User.Image != null)
{
question.FirstAnswer.User.Image = _urlBuilder.BuildUserImageEndpoint(question.FirstAnswer.User.Id, question.FirstAnswer.User.Image);
}
result.Add(question);
break;
case "d":
var document = _jsonSerializer.Deserialize <IEnumerable <DocumentFeedDto> >(v, JsonConverterTypes.TimeSpan).First();
if (document.User.Image != null)
{
document.User.Image =
_urlBuilder.BuildUserImageEndpoint(document.User.Id, document.User.Image);
}
result.Add(document);
break;
}
} while (reader.Read());
}
}
return(result);
}
public async Task <UserProfileAboutDto> GetAsync(UserProfileAboutQuery query, CancellationToken token)
{
var result = _statelessSession.Query <TutorReview>()
.Fetch(f => f.User)
.Where(w => w.Tutor.Id == query.UserId)
.Where(w => w.Review != null && w.Review != string.Empty)
.OrderByDescending(x => x.DateTime)
.Select(s => new TutorReviewDto()
{
Id = s.User.Id,
Image = s.User.ImageName,
ReviewText = s.Review,
Rate = s.Rate,
Created = s.DateTime,
Name = s.User.Name
})
.ToFuture();
var rates = _statelessSession.Query <TutorReview>()
.Where(w => w.Tutor.Id == query.UserId)
.GroupBy(g => Math.Floor(g.Rate))
.Select(s => new RatesDto()
{
Rate = (int)Math.Floor(s.Key),
Users = s.Count()
}).ToFuture();
var reviews = await result.GetEnumerableAsync(token);
return(new UserProfileAboutDto()
{
Reviews = reviews.Select(s =>
{
s.Image = _urlBuilder.BuildUserImageEndpoint(s.Id, s.Image);
return s;
}),
Rates = await rates.GetEnumerableAsync(token)
});
// using (var conn = _repository.OpenConnection())
// {
// using (var grid = await conn.QueryMultipleAsync(@"
//select CourseId as Name from sb.UsersCourses uc
//where UserId = @id
//and ( 1 = case when exists ( select * from sb.Tutor t where t.Id = uc.UserId)
// then uc.canTeach
// else 1
//end);
//select t.bio
//from sb.Tutor t
//where t.Id = @id;
//select tr.Review as ReviewText, tr.Rate, tr.DateTime as Created, u.Name, u.Image, u.Score,u.Id
//from sb.Tutor t
//join sb.TutorReview tr
// on tr.TutorId = t.Id
//join sb.[user] u
// on tr.UserId = U.Id
//where t.Id = @id", new { id = query.UserId }))
// {
// var retVal = new UserProfileAboutDto
// {
// Courses = await grid.ReadAsync<CourseDto>(),
// Bio = await grid.ReadSingleOrDefaultAsync<string>(),
// Reviews = await grid.ReadAsync<TutorReviewDto>()
// };
// return retVal;
// }
// }
}
public async Task <IEnumerable <TutorCardDto> > GetAsync(TutorListByCourseQuery query, CancellationToken token)
{
if (query.Count == 0)
{
throw new ArgumentException("query count cannot be 0");
}
//TODO maybe we can fix this query
ReadTutor tutorAlias = null;
UserCourse userCourseAlias = null;
Course courseAlias = null;
TutorCardDto tutorCardDtoAlias = null;
var relevantTutorByCourse = QueryOver.Of(() => tutorAlias)
.JoinEntityAlias(() => userCourseAlias,
() => userCourseAlias.User.Id == tutorAlias.Id)
.Where(() => userCourseAlias.IsTeach)
.And(() => userCourseAlias.Course.Id == query.CourseId)
.Select(s => s.Id)
.Take(query.Count);
var relevantTutorBySubject = QueryOver.Of(() => tutorAlias)
.JoinEntityAlias(() => userCourseAlias,
() => userCourseAlias.User.Id == tutorAlias.Id)
.JoinAlias(() => userCourseAlias.Course, () => courseAlias)
.Where(() => userCourseAlias.IsTeach)
.WithSubquery.WhereProperty(() => courseAlias.Subject.Id).Eq(
QueryOver.Of <Course>().Where(w => w.Id == query.CourseId)
.Select(s => s.Subject.Id))
.Select(s => s.Id)
.Take(query.Count);
var futureCourse = _session.QueryOver <ReadTutor>()
.WithSubquery.WhereProperty(w => w.Id).In(relevantTutorByCourse)
.Where(w => w.Id != query.UserId)
.And(w => w.Country == query.Country)
.SelectList(s =>
s.Select(x => x.Id).WithAlias(() => tutorCardDtoAlias.UserId)
.Select(x => x.Name).WithAlias(() => tutorCardDtoAlias.Name)
.Select(x => x.ImageName).WithAlias(() => tutorCardDtoAlias.Image)
.Select(x => x.Courses).WithAlias(() => tutorCardDtoAlias.Courses)
.Select(x => x.Subjects).WithAlias(() => tutorCardDtoAlias.Subjects)
.Select(x => x.Price).WithAlias(() => tutorCardDtoAlias.Price)
.Select(x => x.Rate).WithAlias(() => tutorCardDtoAlias.Rate)
.Select(x => x.RateCount).WithAlias(() => tutorCardDtoAlias.ReviewsCount)
.Select(x => x.Bio).WithAlias(() => tutorCardDtoAlias.Bio)
.Select(x => x.University).WithAlias(() => tutorCardDtoAlias.University)
.Select(x => x.Country).WithAlias(() => tutorCardDtoAlias.Country)
.Select(x => x.Lessons).WithAlias(() => tutorCardDtoAlias.Lessons)
.Select(x => x.SubsidizedPrice).WithAlias(() => tutorCardDtoAlias.DiscountPrice))
.OrderBy(o => o.OverAllRating).Desc
.TransformUsing(Transformers.AliasToBean <TutorCardDto>())
.Take(query.Count).Future <TutorCardDto>();
var futureCourse2 = _session.QueryOver <ReadTutor>()
.WithSubquery.WhereProperty(w => w.Id).In(relevantTutorBySubject)
.Where(w => w.Id != query.UserId)
.And(w => w.Country == query.Country)
.SelectList(s =>
s.Select(x => x.Id).WithAlias(() => tutorCardDtoAlias.UserId)
.Select(x => x.Name).WithAlias(() => tutorCardDtoAlias.Name)
.Select(x => x.ImageName).WithAlias(() => tutorCardDtoAlias.Image)
.Select(x => x.Courses).WithAlias(() => tutorCardDtoAlias.Courses)
.Select(x => x.Subjects).WithAlias(() => tutorCardDtoAlias.Subjects)
.Select(x => x.Price).WithAlias(() => tutorCardDtoAlias.Price)
.Select(x => x.Rate).WithAlias(() => tutorCardDtoAlias.Rate)
.Select(x => x.RateCount).WithAlias(() => tutorCardDtoAlias.ReviewsCount)
.Select(x => x.Bio).WithAlias(() => tutorCardDtoAlias.Bio)
.Select(x => x.Country).WithAlias(() => tutorCardDtoAlias.Country)
.Select(x => x.University).WithAlias(() => tutorCardDtoAlias.University)
.Select(x => x.Lessons).WithAlias(() => tutorCardDtoAlias.Lessons))
.OrderBy(o => o.OverAllRating).Desc
.TransformUsing(Transformers.AliasToBean <TutorCardDto>())
.Take(query.Count).Future <TutorCardDto>();
var tutors = await futureCourse.GetEnumerableAsync(token);
var tutors2 = await futureCourse2.GetEnumerableAsync(token);
return(tutors.Union(tutors2).Skip(query.Page * query.Count).Take(query.Count)
.Distinct(TutorCardDto.UserIdComparer)
.Select(s =>
{
s.Image = _urlBuilder.BuildUserImageEndpoint(s.UserId, s.Image);
return s;
}));
}
