Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
            }
        }
Ejemplo n.º 5
0
        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);
            }
        }
Ejemplo n.º 6
0
        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()));
            }
        }