public async Task <IEnumerable <UserPurchasedDocsDto> > GetAsync(UserPurchasedDocsQuery query, CancellationToken token)
{
const string sql = @"select DocumentId, D.Name as Title, U.Name as University, D.CourseName as Class, T.Price
from sb.[Transaction] T
join sb.Document D
on T.DocumentId = D.Id
join sb.University U
on D.UniversityId = U.Id
where User_Id = @Id and TransactionType = 'Document' and T.[Type] = 'Spent'
and User_Id in (select Id from sb.[user] where Id = User_Id and (Country = @Country or @Country is null))
order by 1
OFFSET @pageSize * @PageNumber ROWS
FETCH NEXT @pageSize ROWS ONLY;";
using (var connection = _dapper.OpenConnection())
{
return(await connection.QueryAsync <UserPurchasedDocsDto>(sql,
new
{
id = query.UserId,
PageNumber = query.Page,
PageSize,
query.Country
}));
}
}
public async Task <IEnumerable <RequestTutorAdminEmailDto> > GetAsync(RequestTutorAdminEmailQuery query, CancellationToken token)
{
const string sql = @"select l.CourseId as CourseName,
ut.Name as TutorName,
u.PhoneNumberHash as UserPhone,
u.Id as UserId
from sb.[Lead] l
join sb.ChatRoomAdmin cra
on l.Id = cra.LeadId
join sb.ChatRoom cr
on cra.Id = cr.Id
join sb.ChatUser cu
on cu.ChatRoomId = cra.Id and cu.UserId != l.UserId
join sb.Tutor t
on t.Id = cu.UserId
join sb.[User] as ut
on t.Id = ut.Id
join sb.[user] u
on l.UserId = u.Id and u.PhoneNumberConfirmed = 1 and u.Country = 'il'
where l.Id = @LeadId";
using (var conn = _dapper.OpenConnection())
{
var res = await conn.QueryAsync <RequestTutorAdminEmailDto>(sql, new
{
leadId = query.LeadId
});
return(res);
}
}
public async Task <IEnumerable <ConversationDetailsDto> > GetAsync(ConversationDetailsQuery query, CancellationToken token)
{
string sql = @"with cte as (
select u.Id as UserId, cr.Id as ChatRoomId, u.Name as UserName, u.Email, u.PhoneNumberHash as PhoneNumber, u.ImageName as Image,
case when u.Id = (select top 1 UserId from sb.ChatMessage cm where cm.ChatRoomId = cr.Id order by cm.CreationTime)
then 1
else 0 end as Student,
u.Country
from sb.ChatRoom cr
join sb.ChatUser cu
on cu.ChatRoomId = cr.Id
join sb.[User] u
on u.Id = cu.UserId
where cr.identifier = @Id
)
select * from cte";
if (!string.IsNullOrEmpty(query.Country))
{
sql += @" where Student = 1 and @Country = (select Country from cte where Student = 0) or Student = 0
and Country = @Country";
}
sql += @" order by case when UserId = (select top 1 UserId from sb.ChatMessage cm where cm.ChatRoomId = ChatRoomId order by cm.CreationTime) then 1
else 0 end";
using (var connection = _dapper.OpenConnection())
{
var res = await connection.QueryAsync <ConversationDetailsDto>(sql, new { query.Id, query.Country });
return(res);
}
}
public async Task <IEnumerable <StudyRoomDto> > GetAsync(StudyRoomQuery query, CancellationToken token)
{
var sql = @"Select S.Id as SessionId,
T.[name] TutorName,
U.[Name] UserName,
S.created Created,
isnull(cast(DATEDIFF(minute, s.Created, s.Ended) as nvarchar(10)), 'OnGoing') Duration,
R.TutorId,
U.Id as UserId
from [sb].[StudyRoomSession] S
join [sb].[StudyRoom] R
on S.Studyroomid=R.id
Join sb.[User] T
on T.Id = R.TutorId
join sb.StudyRoomUser sru
on sru.StudyRoomId = R.Id and sru.UserId != T.Id
Join Sb.[user] u
on sru.UserId = U.Id
where U.email not like '%cloudents%'
and U.email not like '%spitball%'
and (DATEDIFF(minute, s.Created, s.Ended) != 0 or DATEDIFF(minute, s.Created, s.Ended) is null)";
if (!string.IsNullOrEmpty(query.Country))
{
sql += " and T.Country = @Country";
}
sql += " order by S.created desc, S.Id";
using (var connection = _dapper.OpenConnection())
{
return(await connection.QueryAsync <StudyRoomDto>(sql, new { query.Country }));
//return res.AsList();
}
}
public async Task <IEnumerable <SessionDto> > GetAsync(SessionsQuery query, CancellationToken token)
{
string sql = @"select cast (S.created as date) as Created,
sum(s.DurationInMinutes) as Duration,
T.Name as Tutor,
U.Name as Student
from [sb].[StudyRoomSession] S
join [sb].[StudyRoom] R
on S.Studyroomid=R.id
join sb.[user] T
on T.Id = R.TutorId
join sb.StudyRoomUser sru
on sru.StudyRoomId = R.Id and sru.UserId != T.Id
join sb.[user] u
on sru.UserId = U.Id
where S.Ended is not null and u.Id = @UserId";
if (!string.IsNullOrEmpty(query.Country))
{
sql += " and u.Country = @Country";
}
sql += @" group by cast (S.created as date),T.Name,U.Name
order by cast(S.created as date) desc;";
using (var connection = _dapper.OpenConnection())
{
var res = await connection.QueryAsync <SessionDto>(sql, new { query.UserId, query.Country });
return(res);
}
}
private async Task ChangeOnlineStatus(long currentUserId, bool isOnline)
{
try
{
if (_canUpdateDb)
{
const string sql = @"update sb.[user] set Online = @IsOnline, LastOnline = GETUTCDATE() where Id = @Id";
using (var connection = _dapper.OpenConnection())
{
await connection.ExecuteAsync(sql, new { Id = currentUserId, IsOnline = isOnline });
}
}
}
catch (Exception e)
{
_logger.Value.TrackException(e, new Dictionary <string, string>()
{
["SignalR"] = "Signalr",
["currentUserId"] = "currentUserId"
});
_canUpdateDb = false;
}
var message = new SignalRTransportType(SignalRType.User, SignalREventAction.OnlineStatus,
new
{
id = currentUserId,
online = true
});
await Clients.All.SendAsync(MethodName, message);
}
public async Task <IEnumerable <CourseDto> > GetAsync(CourseSearchQuery query, CancellationToken token)
{
const int pageSize = 30;
const string sql =
@"declare @country nvarchar(2) = (select country from sb.[user] where id = @Id);
declare @schoolType nvarchar(50) = (select case when UserType = 'UniversityStudent' then 'University'
when UserType in ('HighSchoolStudent', 'Parent') then 'HighSchool'
else null end
from sb.[user] where Id = @Id);
with cte as
(
select distinct CourseId
from sb.UsersCourses uc1
join sb.[user] u1
on u1.Id = uc1.UserId
join sb.University un
on un.Id = u1.UniversityId2
where un.Country = @Country
)
select Name,
1 as IsFollowing,
c.count as Students
from sb.Course c
join sb.UsersCourses uc
on c.Name = uc.CourseId and uc.UserId = @Id
where State = 'OK'
union all
select Name,
0 as IsFollowing,
c.count as Students
from sb.Course c
where State = 'OK'
and ( @schoolType is null
or (@schoolType = 'University' and c.SchoolType is null)
or c.SchoolType = @schoolType )
and exists (select CourseId from cte where CourseId = c.Name)
and not exists (select uc.CourseId from sb.UsersCourses uc where c.Name = uc.CourseId and uc.UserId = @Id)
and (c.Country is null or c.Country = @Country)
order by IsFollowing desc,
c.count desc
OFFSET @PageSize * @Page ROWS
FETCH NEXT @PageSize ROWS ONLY;";
using (var conn = _dapperRepository.OpenConnection())
{
return(await conn.QueryAsync <CourseDto>(sql, new
{
Id = query.UserId,
PageSize = pageSize,
query.Page
}));
}
}
public async Task <UserReferralsDto> GetAsync(UserReferralsQuery query, CancellationToken token)
{
using (var connection = _dapper.OpenConnection())
{
return(await connection.QueryFirstOrDefaultAsync <UserReferralsDto>(@"select count(1) as Referrals
from sb.[Transaction] where [Action] = 'ReferringUser' and User_Id = @Id"
, new { query.Id }));
}
}
//private readonly QuerySession _querySession;
//public RequestTutorEmailQueryHandler(QuerySession querySession)
//{
// _querySession = querySession;
//}
public async Task <IEnumerable <RequestTutorEmailDto> > GetAsync(RequestTutorEmailQuery query, CancellationToken token)
{
const string sql = @"select
u.[Language] as TutorLanguage,
l.Text as Request,
us.PhoneNumberHash as StudentPhoneNumber,
us.Name as StudentName,
us.Id as StudentId,
l.CourseId as CourseName,
u.FirstName as TutorFirstName,
t.Id as TutorId,
u.Email as TutorEmail,
u.Country as TutorCountry
from sb.ChatRoomAdmin cra
join sb.[Lead] l
on cra.LeadId = l.Id
join sb.ChatUser cu
on cu.ChatRoomId = cra.Id and cu.UserId != l.UserId
join sb.[user] u
on cu.UserId = u.Id and u.PhoneNumberConfirmed = 1
join sb.Tutor t
on t.Id = u.Id
join sb.[user] us
on us.Id = l.UserId
where cra.LeadId = @leadId";
using (var conn = _dapper.OpenConnection())
{
var res = await conn.QueryAsync <RequestTutorEmailDto>(sql, new
{
leadId = query.LeadId
});
return(res);
}
//return await _querySession.StatelessSession.Query<Lead>()
// .Fetch(f => f.Tutor).ThenFetch(f => f.User)
// .Fetch(f => f.User)
// .Where(w => w.Id == query.LeadId).Select(s => new RequestTutorEmailDto
// {
// TutorLanguage = s.Tutor.User.Language,
// CourseName = s.Course.Id,
// Request = s.Text,
// TutorId = s.Tutor.Id,
// TutorEmail = s.Tutor.User.Email,
// StudentName = s.User.Name,
// TutorFirstName = s.Tutor.User.FirstName,
// StudentPhoneNumber = s.User.PhoneNumber,
// TutorCountry = s.Tutor.User.Country
// }).ToListAsync(token);
}
public async Task <IActionResult> MigrateUniversity([FromBody] MigrateUniversityRequest model,
CancellationToken token)
{
const string update = @"update sb.[user] set UniversityId2 = @NewUni where UniversityId2 = @OldUni;
update sb.Document set UniversityId = @NewUni where UniversityId = @OldUni;
update sb.Question set UniversityId = @NewUni where UniversityId = @OldUni;
delete from sb.University where id = @OldUni;";
using (var connection = _dapperRepository.OpenConnection())
{
await connection.ExecuteAsync(update, new
{
NewUni = model.UniversityToKeep,
OldUni = model.UniversityToRemove
});
}
/*var command = new MigrateUniversityCommand(model.UniversityToKeep, model.UniversityToRemove);
* await _commandBus.DispatchAsync(command, token);*/
return(Ok());
}
public async Task <IActionResult> MigrateCourse([FromBody] MigrateCourseRequest model,
CancellationToken token)
{
const string update = @"
update sb.Document
set CourseName = @newId
where CourseName = @oldId;
update sb.Question
set CourseId = @newId
where CourseId = @oldId;
update sb.UsersCourses
set CourseId = @newId
where CourseId = @oldId
and UserId not in (select UserId from sb.UsersCourses where CourseId = @newId);
delete from sb.UsersCourses where CourseId = @oldId;
delete from sb.Course where [Name] = @oldId;";
using (var connection = _dapperRepository.OpenConnection())
{
await connection.ExecuteAsync(update, new
{
newId = model.CourseToKeep,
oldId = model.CourseToRemove
});
}
/*var command = new MigrateCourseCommand(model.CourseToKeep, model.CourseToRemove);
* var deleteCommand = new DeleteCourseCommand(model.CourseToRemove);
* await _commandBus.DispatchAsync(command, token);
* await _commandBus.DispatchAsync(deleteCommand, token);*/
return(Ok());
}
public async Task <IEnumerable <SubjectDto> > GetAsync(SubjectsTranslationQuery query,
CancellationToken token)
{
const string sql = @"select cs.Id, cs.Name as HeName, st.NameTranslation as EnName
from sb.CourseSubject cs
left join sb.SubjectTranslation st
on cs.Id = st.SubjectId
and st.LanguageId = (select Id from sb.AdminLanguage where Name = 'en')" ;
using (var conn = _dapperRepository.OpenConnection())
{
return(await conn.QueryAsync <SubjectDto>(sql));
}
}
public async Task <ChatUserDto> GetAsync(ChatConversationQuery query, CancellationToken token)
{
using (var conn = _dapper.OpenConnection())
{
return(await conn.QueryFirstOrDefaultAsync <ChatUserDto>(@"
Select u.Name,u.Id as UserId,u.ImageName as Image,u.Online,cu.Unread, cr.Identifier as ConversationId, cr.UpdateTime as DateTime
from sb.ChatUser cu
join sb.ChatRoom cr on cu.ChatRoomId = cr.Id
join sb.ChatUser cu2 on cu2.ChatRoomId = cr.Id and cu2.Id <> cu.Id
join sb.[User] u on cu2.UserId = u.Id
where cu.UserId = @UserId and cr.Identifier = @Id
order by cr.UpdateTime desc", new { id = query.Id, query.UserId }));
}
}
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 static async Task UpdateReviewTextAsync([TimerTrigger("0 0 1 * * *")] TimerInfo myTimer,
[Inject] IDapperRepository dapperRepository,
ILogger log)
{
using var openConnection = dapperRepository.OpenConnection();
const string sql = @" update tr
set Review = x.[text]
from sb.TutorReview tr
join
(
Select tri.Id, case when floor(Rate) = 1 then N'Not recommended'
when floor(Rate) = 2 then N'Ok'
when floor(Rate) = 3 then N'Good'
when floor(Rate) = 4 then N'Very Good'
when floor(Rate) = 5 then N'Excellent' end as Text
from sb.TutorReview tri
join sb.tutor t on tri.TutorId = t.id
join sb.[user] u on u.id = t.id
where tri.Review is null
and u.Country != 'IL'
) x
on x.Id = tr.Id";
var result = await openConnection.ExecuteAsync(sql);
log.LogInformation($"update non IL amount: {result}");
const string ilSQl = @"update tr
set Review = x.[text]
from sb.TutorReview tr
join
(
Select tri.Id, case when floor(tri.Rate) = 1 then N'לא מומלץ'
when floor(tri.Rate) = 2 then N'בסדר'
when floor(tri.Rate) = 3 then N'טוב'
when floor(tri.Rate) = 4 then N'טוב מאוד'
when floor(tri.Rate) = 5 then N'מצויין' end as [text]
from sb.TutorReview tri
join sb.tutor t on tri.TutorId = t.id
join sb.[user] u on u.id = t.id
where tri.Review is null
and u.Country = 'IL'
) x
on x.Id = tr.Id";
result = await openConnection.ExecuteAsync(ilSQl);
log.LogInformation($"update IL amount: {result}");
}
public async Task <IEnumerable <string> > GetAsync(SubjectsQuery query,
CancellationToken token)
{
const string sql = @"select COALESCE(st.NameTranslation, cs.Name)
from sb.CourseSubject cs
left join sb.SubjectTranslation st
on cs.Id = st.SubjectId
and st.LanguageId = (select LanguageId from sb.AdminUser where Id = @Id)
order by COALESCE(st.NameTranslation, cs.Name)";
using (var conn = _dapperRepository.OpenConnection())
{
return(await conn.QueryAsync <string>(sql, new { id = query.AdminUserId }));
}
}
public async Task <IEnumerable <ChatMessageDto> > GetAsync(ChatConversationByIdQuery query, CancellationToken token)
{
using (var conn = _repository.OpenConnection())
{
var result = new List <ChatMessageDto>();
var reader = await conn.ExecuteReaderAsync(@"
Select
messageType as discriminator, cm.userId,message as Text,cm.creationTime as DateTime , blob as Attachment,
cm.id as id, cr.Id as chatRoomId, u.ImageName as Image, u.Name,
case when cu.Unread >= ROW_NUMBER() OVER(PARTITION BY cm.userId ORDER BY cm.Id desc) then 1 else 0 end as Unread
from sb.ChatMessage cm
join sb.ChatRoom cr
on cm.ChatRoomId = cr.Id
join sb.[user] u
on cm.UserId = u.Id
join sb.ChatUser cu
on cu.ChatRoomId = cr.Id and cu.UserId != cm.UserId
join sb.[user] tu
on tu.Id = cu.UserId
where cr.Identifier = @Id and (u.Country = @Country or tu.Country = @Country or @Country is null)
order by cm.Id desc
OFFSET @PageSize * @PageNumber ROWS
FETCH NEXT @PageSize ROWS ONLY;", new { Id = query.ConversationId, query.Country, PageSize = 50, PageNumber = query.Page });
if (reader.Read())
{
var toMessage = reader.GetRowParser <ChatMessageDto>(typeof(ChatTextMessageDto));
var toAttachment = reader.GetRowParser <ChatMessageDto>(typeof(ChatAttachmentDto));
var col = reader.GetOrdinal("discriminator");
do
{
switch (reader.GetString(col))
{
case "text":
result.Add(toMessage(reader));
break;
case "attachment":
result.Add(toAttachment(reader));
break;
}
} while (reader.Read());
}
return(result);
}
}
public async Task <IEnumerable <AboutTutorDto> > GetAsync(AboutTutorQuery query, CancellationToken token)
{
const string sql = @"select top 10 u.Id as UserId, u.Name, u.ImageName as Image, tr.Rate, tr.Review
from sb.TutorReview tr
join sb.Tutor t
on tr.TutorId = t.Id
join sb.[User] u
on t.Id = u.Id
where len(tr.Review) > 0 and tr.Rate > 3
order by NEWID()";
using (var conn = _dapperRepository.OpenConnection())
{
var retVal = await conn.QueryAsync <AboutTutorDto>(sql);
return(retVal);
}
}
public async Task <IEnumerable <CourseDto> > GetAsync(CourseSearchQuery query, CancellationToken token)
{
const int pageSize = 50;
const string sql =
@"
Select @Term = case when @Term is null then '""""' else '""' + @Term+ '*""' end
select c.Name,
case when uc.CourseId is not null then 1 else null end as IsFollowing,
c.count as Students
from sb.Course c
left join sb.UsersCourses uc
on c.Name = uc.CourseId and uc.UserId = @Id
where (@Term = '""""' or Contains(Name, @Term))
and (c.name in (
select CourseId
from sb.UsersCourses uc1
join sb.[user] u
on u.Id = uc1.UserId
where (u.Country = @Country or @Country is null)
and uc1.CourseId = c.Name
)
or
(select count(1) from sb.UsersCourses uc1 where uc1.CourseId = c.Name) = 0
)
and State = 'OK'
order by case when uc.CourseId is not null
then 1 else null end desc,
c.count desc
OFFSET @PageSize * @Page ROWS
FETCH NEXT @PageSize ROWS ONLY;";
using (var conn = _dapperRepository.OpenConnection())
{
return(await conn.QueryAsync <CourseDto>(sql, new
{
query.Term,
query.Country,
Id = query.UserId,
PageSize = pageSize,
query.Page
}));
}
}
public async Task <IEnumerable <CourseDto> > GetAsync(UserCoursesQuery query, CancellationToken token)
{
token.ThrowIfCancellationRequested();
//We use Students, IsPending and IsTeaching in "My Courses" when a user edit his courses list
const string sql = @"select CourseId as [Name],
c.count as Students,
case when c.State = 'Pending' then 1 else null end as IsPending,
uc.CanTeach as IsTeaching
from sb.UsersCourses uc
join sb.Course c
on uc.courseId = c.Name
where UserId = @Id
order by IsPending desc, Students desc";
using (var conn = _dapperRepository.OpenConnection())
{
return(await conn.QueryAsync <CourseDto>(sql, new { Id = query.UserId }));
}
}
public async Task <IEnumerable <PendingTutorsDto> > GetAsync(PendingTutorsQuery query, CancellationToken token)
{
const string sql = @"select u.Id, u.FirstName, u.LastName,
u.Email, t.Bio, t.Price, t.Created,
(select STRING_AGG(dt.CourseId, ', ') FROM(select top 10 courseId
from sb.UsersCourses dt
where u.Id = dt.UserId and dt.CanTeach = 1) dt) as courses,
u.ImageName as Image
from sb.[User] u
join sb.Tutor t
on u.Id = t.Id
where t.State = 'Pending' AND (@Country IS NULL OR u.Country = @Country) order by t.id desc OPTION(RECOMPILE)";
using (var connection = _dapper.OpenConnection())
{
var res = await connection.QueryAsync <PendingTutorsDto>(sql, new { query.Country });
return(res);
}
}
public async Task <PaymentDetailDto> GetAsync(PaymentBySessionIdQuery query, CancellationToken token)
{
//This query will not work in case there will be more then one student in a room.
const string sql = @"select case when t.Price is null then tr.Price else t.Price end as TutorPricePerHour,
x.CouponCode as CouponCode,
x.couponType,
x.CouponValue as CouponValue,
x.CouponTutor as CouponTutor
from [sb].[StudyRoomSession] srs
join sb.StudyRoom sr
on srs.StudyRoomId = sr.Id
left join sb.TutorHistory t
on sr.TutorId = t.Id and srs.Created between t.BeginDate and t.EndDate
join sb.Tutor tr
on tr.Id = sr.TutorId
join sb.StudyRoomUser sru
on srs.StudyRoomId = sru.StudyRoomId and sru.userId != tr.Id
join sb.[user] u
on u.id = sru.UserId
outer apply (
Select c.code as CouponCode,
c.couponType,
c.Value as CouponValue,
c.tutorId as CouponTutor
from sb.userCoupon uc
join sb.coupon c on uc.couponId = c.id and uc.UsedAmount < c.AmountOfUsePerUser
where u.id = uc.userid and tr.id = uc.tutorId
) x
join sb.[User] tu
on tr.Id = tu.Id
where srs.id = @id";
using (var conn = _repository.OpenConnection())
{
var result = await conn.QuerySingleAsync <PaymentDetailDto>(sql, new { id = query.SessionId });
return(result);
}
}
public async Task <IList <PendingCoursesDto> > GetAsync(CoursesQuery query, CancellationToken token)
{
var sql = @"Select @Term = case when @Term is null then '""""' else '""*' + @Term + '*""' end
select distinct top 100 c.Name
from sb.Course c
join sb.UsersCourses uc
on c.Name = uc.CourseId
join sb.[User] u
on uc.UserId = u.Id
where c.State = @State
and (@Term = '""""' or CONTAINS(c.Name, @Term))";
if (!string.IsNullOrEmpty(query.Country))
{
sql += " and u.Country = @Country";
}
if (!string.IsNullOrEmpty(query.Language))
{
if (query.Language.Equals("he", StringComparison.OrdinalIgnoreCase))
{
sql += " and c.name like N'%[א-ת]%'";
}
else if (query.Language.Equals("en", StringComparison.OrdinalIgnoreCase))
{
sql += " and c.name like '%[a-z]%'";
}
}
using (var connection = _dapper.OpenConnection())
{
var res = await connection.QueryAsync <PendingCoursesDto>(
sql,
new { State = query.State.ToString(), query.Country, Term = query.Filter }
);
return(res.AsList());
}
}
public async Task <IEnumerable <CourseDto> > GetAsync(CourseSearchWithTermQuery query, CancellationToken token)
{
const int pageSize = 30;
const string sql =
@"
declare @schoolType nvarchar(50) = (select case when UserType = 'UniversityStudent' then 'University'
when UserType in ('HighSchoolStudent', 'Parent') then 'HighSchool'
else null end
from sb.[user] where Id = @Id);
Select @Term = '""*' + @Term+ '*""';
select Name,
case when uc.CourseId is not null then 1 else null end as IsFollowing,
c.count as Students
from sb.Course c
left join sb.UsersCourses uc
on c.Name = uc.CourseId and uc.UserId = @Id
where Contains(Name, @Term)
and State = 'OK'
and ( c.SchoolType = @schoolType
or (@schoolType = 'University' and c.SchoolType is null)
or @schoolType is null )
order by case when uc.CourseId is not null
then 1 else null end desc,
c.count desc
OFFSET @PageSize * @Page ROWS
FETCH NEXT @PageSize ROWS ONLY;";
using (var conn = _dapperRepository.OpenConnection())
{
return(await conn.QueryAsync <CourseDto>(sql, new
{
query.Term,
Id = query.UserId,
PageSize = pageSize,
query.Page
}));
}
}
public async Task <IEnumerable <CashOutDto> > GetAsync(CashOutQuery query, CancellationToken token)
{
var sql = @"select t.User_id as userId,
t.price as CashoutPrice,
u.Email as UserEmail,
t.created as CashOutTime,
t.Approved as Approved,
t.declinedReason DeclinedReason,
t.id as TransactionId,
u.country as Country,
(select count(*) from sb.[Transaction] v where TransactionType = 'Refer' and t.User_id = v.User_id) as ReferCount,
(select count(*) from sb.[Transaction] v where TransactionType = 'Document' and v.type = 'earned' and t.User_id = v.User_id) as SoldDocument,
(select count(*) from sb.[Transaction] v where TransactionType = 'Question' and v.type = 'earned' and t.User_id = v.User_id) as CorrectAnswer,
(select count(*) from sb.[Transaction] v left join sb.Document d on v.DocumentId = d.Id where coalesce( d.State,'deleted') = 'Deleted' and TransactionType = 'Document' and v.type = 'earned' and t.User_id = v.User_id) as SoldDeletedDocument,
(select count(*) from sb.[Transaction] v left join sb.Answer a on v.AnswerId = a.Id where coalesce( a.State,'deleted') = 'Deleted' and TransactionType = 'Question' and v.type = 'earned' and t.User_id = v.User_id) as DeletedCorrectAnswer,
--count cashouts
(select count(*) from sb.[Transaction] v where t.User_id = v.User_id and v.Action = 'CashOut' and v.TransactionType='CashOut') as CashOut,
--count awarded
(select count(*) from sb.[Transaction] v where t.User_id = v.User_id and v.TransactionType='Award') as AwardCount,
--count buy
(select count(*) from sb.[Transaction] v where t.User_id = v.User_id and v.TransactionType='BuyPoints') as BuyCount
from sb.[Transaction] t
join sb.[User] u
on t.User_id = u.Id
where t.TransactionType='CashOut' and t.Action='CashOut' and t.Approved is null and t.Created>@date";
if (!string.IsNullOrEmpty(query.Country))
{
sql += " and u.Country = @Country";
}
sql += " order by t.id desc";
using (var conn = _session.OpenConnection())
{
var result = await conn.QueryAsync <CashOutDto>(sql, new { date = DateTime.UtcNow.AddMonths(-1), query.Country });
return(result);
}
}
public async Task <IEnumerable <EmailObjectDto> > GetAsync(GetEmailByEventQuery query, CancellationToken token)
{
const string sql = @"SELECT JSON_VALUE(template, '$.socialShare') as SocialShare,
JSON_VALUE(template, '$.eventName') as Event,
JSON_VALUE(template, '$.subject') as Subject,
JSON_VALUE(template, '$.cultureInfo') as CultureInfo,
JSON_VALUE(template, '$.blocks[0].title') as Title,
JSON_VALUE(template, '$.blocks[0].subtitle') as Subtitle,
JSON_VALUE(template, '$.blocks[0].body') as Body,
JSON_VALUE(template, '$.blocks[0].cta') as Cta,
JSON_VALUE(template, '$.blocks[0].minorTitle') as MinorTitle,
JSON_VALUE(template, '$.blocks[1].title') as Title,
JSON_VALUE(template, '$.blocks[1].subtitle') as Subtitle,
JSON_VALUE(template, '$.blocks[1].body') as Body,
JSON_VALUE(template, '$.blocks[1].cta') as Cta,
JSON_VALUE(template, '$.blocks[1].minorTitle') as MinorTitle
FROM sb.Email
WHERE JSON_VALUE(template, '$.eventName') = @e";
using (var conn = _dapperRepository.OpenConnection())
{
var flatRes = await conn.QueryAsync <EmailObjectDto, EmailBlockDto, EmailBlockDto, EmailObjectDto>(sql,
(emailDto, blockDto0, blockDto1) =>
{
emailDto.Blocks.Add(blockDto0);
if (blockDto1 != null)
{
emailDto.Blocks.Add(blockDto1);
}
return(emailDto);
}, new
{
e = query.Event,
}, splitOn : "SocialShare,Title,Title");
return(flatRes);
}
}
public async Task <IEnumerable <TutorDto> > GetAsync(TutorSearchQuery query, CancellationToken token)
{
const int pageSize = 200;
const string sqlWithTerm = @"Select @Term = case when @Term is null then '""""' else '""' + @Term+ '*""' end
select u.Id, u.Name, u.Email, u.PhoneNumberHash as PhoneNumber, u.Country, t.State
from sb.Tutor t
join sb.[User] u
on u.Id = t.Id
where CONTAINS(([Name], Email, PhoneNumberHash) , @Term)
where (u.Country = @Country or @Country is null)
and (t.State = @State or @State is null)
order by u.Id
OFFSET @PageSize * @Page ROWS
FETCH NEXT @PageSize ROWS ONLY;";
const string sqlWithoutTerm = @"select u.Id, u.Name, u.Email, u.PhoneNumberHash as PhoneNumber, u.Country, t.State
from sb.Tutor t
join sb.[User] u
on u.Id = t.Id
where (u.Country = @Country or @Country is null)
and (t.State = @State or @State is null)
order by u.Id
OFFSET @PageSize * @Page ROWS
FETCH NEXT @PageSize ROWS ONLY;";
var sql = string.IsNullOrEmpty(query.Term) ? sqlWithoutTerm : sqlWithTerm;
using var conn = _dapperRepository.OpenConnection();
return(await conn.QueryAsync <TutorDto>(sql, new
{
query.Term,
State = query.State?.ToString(),
query.Country,
PageSize = pageSize,
query.Page
}));
}
public async Task <DocumentPurchaseEmailDto> GetAsync(GetDocumentPurchasedEmailQuery query, CancellationToken token)
{
const string sql = @"Select
d.CourseName,
d.Name as documentName ,
u.Email as ToEmailAddress,
u.id as userId,
t.Price as tokens,
u.Language
from sb.[Transaction] t
join sb.[User] u on t.User_id = u.Id
join sb.Document d on t.DocumentId = d.id
where t.id = @id";
using (var connection = _dapper.OpenConnection())
{
return(await connection.QuerySingleAsync <DocumentPurchaseEmailDto>(sql,
new
{
id = query.TransactionId,
}));
}
}
public async Task <IEnumerable <ChatUserDto> > GetAsync(ChatConversationsQuery query, CancellationToken token)
{
using (var conn = _dapper.OpenConnection())
{
var result = await conn.QueryAsync <ChatUserDto>(@"
Select u.Name,
u.Id as UserId,
u.ImageName as Image,
u.Online,
cu.Unread,
cr.Identifier as ConversationId,
cr.UpdateTime as DateTime,
(select id from sb.StudyRoom where Identifier = cr.identifier) as StudyRoomId,
(select top 1 cm.Message from sb.ChatMessage cm where cm.ChatRoomId = cr.Id order by id desc) as lastMessage
from sb.ChatUser cu
join sb.ChatRoom cr on cu.ChatRoomId = cr.Id
join sb.ChatUser cu2 on cu2.ChatRoomId = cr.Id and cu2.Id <> cu.Id
join sb.[User] u on cu2.UserId = u.Id
where cu.UserId = @id
order by cr.UpdateTime desc", new { id = query.UserId });
return(result);
}
}
public async Task <IEnumerable <UpdateUserEmailDto> > GetAsync(GetUpdatesEmailUsersQuery query, CancellationToken token)
{
const string sql = @"Select distinct u.Name as UserName,
u.Email as ToEmailAddress,
u.Language,
u.Id as UserId
from sb.[user] u
join sb.UsersCourses uc on u.id = uc.UserId
join
(Select d.UniversityId as UniversityId,d.CourseName from sb.Document d
where state = 'Ok'
and d.CreationTime > @Since
union
Select q.UniversityId as UniversityId,q.CourseId as CourseName from sb.question q
where state = 'Ok'
and q.Created > @Since
) t
on u.UniversityId2 = t.UniversityId and t.CourseName = uc.CourseId
where u.EmailConfirmed = 1
and u.country = 'IL'
--Temp for now of emails
and u.LastOnline > getUtcDate()-90
order by id
OFFSET @pageSize * @PageNumber ROWS
FETCH NEXT @pageSize ROWS ONLY;";
using (var conn = _session.OpenConnection())
{
return(await conn.QueryAsync <UpdateUserEmailDto>(sql, new
{
query.Since,
PageSize = 100,
PageNumber = query.Page
}));
}
}
