private DapperQuery BaseDomainObjectSearch(DomainType domainType, SearchOptions options)
{
var q = new DapperQuery();
var hasPhrase = !String.IsNullOrEmpty(options.Phrase);
switch (domainType)
{
case DomainType.Subverse:
q.SelectColumns = "@DomainType as \"Type\", s.\"Name\", s.\"Title\", s.\"Description\", s.\"CreatedBy\" AS \"OwnerName\", s.\"SubscriberCount\", s.\"CreationDate\"";
q.Select = $"DISTINCT {"{0}"} FROM {SqlFormatter.Table("Subverse", "s", null, "NOLOCK")}";
if (hasPhrase)
{
q.Where = "(s.\"Name\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Title\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Description\" LIKE CONCAT('%', @SearchPhrase, '%'))";
}
q.Append(x => x.Where, $"s.\"IsAdminDisabled\" = {SqlFormatter.BooleanLiteral(false)} AND s.\"IsPrivate\" = {SqlFormatter.BooleanLiteral(false)}");
break;
case DomainType.Set:
q.SelectColumns = "@DomainType as \"Type\", s.\"Name\", s.\"Title\", s.\"Description\", s.\"UserName\" AS \"OwnerName\", s.\"SubscriberCount\", s.\"CreationDate\"";
q.Select = $"DISTINCT {"{0}"} FROM {SqlFormatter.Table("SubverseSet", "s", null, "NOLOCK")}";
if (hasPhrase)
{
q.Where = "(s.\"Name\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Title\" LIKE CONCAT('%', @SearchPhrase, '%') OR s.\"Description\" LIKE CONCAT('%', @SearchPhrase, '%'))";
}
q.Append(x => x.Where, "(s.\"IsPublic\" = @IsPublic OR @IsPublic IS NULL)");
break;
}
q.Parameters.Add("DomainType", (int)domainType);
q.Parameters.Add("IsPublic", (bool?)true);
if (hasPhrase)
{
q.Parameters.Add("SearchPhrase", options.Phrase);
}
switch (options.Sort)
{
case SortAlgorithm.Active:
switch (domainType)
{
case DomainType.Subverse:
q.Select += $" INNER JOIN {SqlFormatter.Table("Submission", "sub", null, "NOLOCK")} ON sub.\"Subverse\" = s.\"Name\"";
q.SelectColumns += ", MAX(sub.\"CreationDate\") AS \"ThisIsOnlyUsedForSortingByActive\"";
q.GroupBy = "s.\"Name\", s.\"Title\", s.\"Description\", s.\"CreatedBy\", s.\"SubscriberCount\", s.\"CreationDate\"";
q.OrderBy = "MAX(sub.\"CreationDate\") DESC";
break;
case DomainType.Set:
q.Select += $" INNER JOIN {SqlFormatter.Table("SubverseSetList", "subList", null, "NOLOCK")} ON subList.\"SubverseSetID\" = s.\"ID\"";
q.SelectColumns += ", MAX(subList.\"CreationDate\") AS \"ThisIsOnlyUsedForSortingByActive\"";
q.GroupBy = "s.\"Name\", s.\"Title\", s.\"Description\", s.\"UserName\", s.\"SubscriberCount\", s.\"CreationDate\"";
q.OrderBy = "MAX(subList.\"CreationDate\") DESC";
break;
}
break;
case SortAlgorithm.New:
q.OrderBy = "s.\"CreationDate\" DESC";
break;
case SortAlgorithm.Top:
default:
q.OrderBy = "s.\"SubscriberCount\" DESC";
break;
}
q.SkipCount = options.Index;
q.TakeCount = options.Count;
return(q);
}
public async Task <IEnumerable <DomainReferenceDetails> > UserSubscribedSetDetails(string userName, SearchOptions options)
{
var q = BaseDomainObjectSearch(DomainType.Set, options);
q.Select += $" INNER JOIN {SqlFormatter.Table("SubverseSetSubscription", "setSubscription", null, "NOLOCK")} ON setSubscription.\"SubverseSetID\" = s.\"ID\"";
q.Append(x => x.Where, "setSubscription.\"UserName\" = @UserName");
q.Parameters.Add("UserName", userName);
//Reset ispublic on base query
q.Parameters.Add("IsPublic", (bool?)null);
var query = q.ToString();
var results = await _db.Connection.QueryAsync <DomainReferenceDetails>(query, q.Parameters);
return(results);
}
public async Task <IEnumerable <DomainReferenceDetails> > SearchDomainObjects(DomainType domainType, SearchOptions options)
{
var q = BaseDomainObjectSearch(domainType, options);
var query = q.ToString();
var results = await _db.Connection.QueryAsync <DomainReferenceDetails>(query, q.Parameters);
return(results);
}
public async Task <IEnumerable <Domain.Models.CommentRemovalLog> > GetModLogRemovedComments(string subverse, SearchOptions options)
{
using (var db = new VoatDataContext(CONSTANTS.CONNECTION_READONLY))
{
var data = (from b in db.CommentRemovalLog
join c in db.Comment on b.CommentID equals c.ID
join s in db.Submission on c.SubmissionID equals s.ID
where s.Subverse.ToLower() == subverse.ToLower()
select b).Include(x => x.Comment).Include(x => x.Comment.Submission);
var data_ordered = data.OrderByDescending(x => x.CreationDate).Skip(options.Index).Take(options.Count);
var results = await data_ordered.ToListAsync().ConfigureAwait(CONSTANTS.AWAIT_CAPTURE_CONTEXT);
//TODO: Move to DomainMaps
var mapToDomain = new Func <Data.Models.CommentRemovalLog, Domain.Models.CommentRemovalLog>(d =>
{
var m = new Domain.Models.CommentRemovalLog();
m.CreatedBy = d.Moderator;
m.Reason = d.Reason;
m.CreationDate = d.CreationDate;
m.Comment = new SubmissionComment();
m.Comment.ID = d.Comment.ID;
m.Comment.UpCount = (int)d.Comment.UpCount;
m.Comment.DownCount = (int)d.Comment.DownCount;
m.Comment.Content = d.Comment.Content;
m.Comment.FormattedContent = d.Comment.FormattedContent;
m.Comment.IsDeleted = d.Comment.IsDeleted;
m.Comment.CreationDate = d.Comment.CreationDate;
m.Comment.IsAnonymized = d.Comment.IsAnonymized;
m.Comment.UserName = m.Comment.IsAnonymized ? d.Comment.ID.ToString() : d.Comment.UserName;
m.Comment.LastEditDate = d.Comment.LastEditDate;
m.Comment.ParentID = d.Comment.ParentID;
m.Comment.Subverse = d.Comment.Submission.Subverse;
m.Comment.SubmissionID = d.Comment.SubmissionID;
m.Comment.Submission.Title = d.Comment.Submission.Title;
m.Comment.Submission.IsAnonymized = d.Comment.Submission.IsAnonymized;
m.Comment.Submission.UserName = m.Comment.Submission.IsAnonymized ? d.Comment.Submission.ID.ToString() : d.Comment.Submission.UserName;
m.Comment.Submission.IsDeleted = d.Comment.Submission.IsDeleted;
return(m);
});
var mapped = results.Select(mapToDomain).ToList();
return(mapped);
}
}
public async Task <IEnumerable <Data.Models.SubmissionRemovalLog> > GetModLogRemovedSubmissions(string subverse, SearchOptions options)
{
using (var db = new VoatDataContext(CONSTANTS.CONNECTION_READONLY))
{
var data = (from b in db.SubmissionRemovalLog
join s in db.Submission on b.SubmissionID equals s.ID
where s.Subverse.ToLower() == subverse.ToLower()
select new SubmissionRemovalLog()
{
SubmissionID = b.SubmissionID,
Submission = s,
CreationDate = b.CreationDate,
Moderator = b.Moderator,
Reason = b.Reason
});
var data2 = data.OrderByDescending(x => x.CreationDate).Skip(options.Index).Take(options.Count);
var results = await data2.ToListAsync().ConfigureAwait(CONSTANTS.AWAIT_CAPTURE_CONTEXT);
return(results);
}
}
public async Task <Tuple <int, IEnumerable <Domain.Models.SubverseBan> > > GetModLogBannedUsers(string subverse, SearchOptions options)
{
using (var db = new VoatDataContext(CONSTANTS.CONNECTION_READONLY))
{
var data = (from b in db.SubverseBan
where b.Subverse.ToLower() == subverse.ToLower()
select new Domain.Models.SubverseBan
{
CreatedBy = b.CreatedBy,
CreationDate = b.CreationDate,
Reason = b.Reason,
Subverse = b.Subverse,
ID = b.ID,
UserName = b.UserName
});
//This is nasty imo
var count = data.Count();
data = data.OrderByDescending(x => x.CreationDate).Skip(options.Index).Take(options.Count);
var results = await data.ToListAsync().ConfigureAwait(CONSTANTS.AWAIT_CAPTURE_CONTEXT);
return(Tuple.Create(count, results.AsEnumerable()));
}
}