Beispiel #1
0
        public SubverseSet GetSet(string name, string userName, SetType?type = null, bool createIfMissing = false)
        {
            var normalization = Normalization.Lower;

            var q = new DapperQuery();

            q.Select = $"SELECT * FROM {SqlFormatter.Table("SubverseSet", "subSet")}";
            q.Where  = SqlFormatter.ToNormalized("subSet.\"Name\"", normalization) + " = @Name";

            if (type.HasValue)
            {
                q.Append(x => x.Where, "subSet.\"Type\" = @Type");
            }

            if (!String.IsNullOrEmpty(userName))
            {
                q.Append(x => x.Where, SqlFormatter.ToNormalized("subSet.\"UserName\"", normalization) + " = @UserName");
            }
            else
            {
                q.Append(x => x.Where, "subSet.\"UserName\" IS NULL");
            }

            q.Parameters = new DynamicParameters(new {
                Name     = name.ToNormalized(normalization),
                UserName = userName.ToNormalized(normalization),
                Type     = (int?)type
            });

            using (var db = new VoatDataContext())
            {
                var set = db.Connection.QueryFirstOrDefault <SubverseSet>(q.ToString(), q.Parameters);
                return(set);
            }
        }
        private int UserCommentCount(string userName, DateRange range = null, string subverse = null, bool?isDeleted = null)
        {
            if (range == null)
            {
                range = new DateRange();
            }
            //var result = (from x in _db.Comment
            //              where
            //                x.UserName.Equals(userName, StringComparison.OrdinalIgnoreCase)
            //                && (x.Submission.Subverse.Equals(subverse, StringComparison.OrdinalIgnoreCase) || subverse == null)
            //                && (
            //                    (startDate.HasValue && x.CreationDate >= startDate.Value)
            //                    &&
            //                    (endDate.HasValue && x.CreationDate <= endDate.Value)
            //                    )
            //              select x).Count();
            var q = new DapperQuery();

            q.Select = $"COUNT(*) FROM {SqlFormatter.Table("Comment", "c", null, "NOLOCK")}";
            q.Where  = "c.\"UserName\" = @UserName";
            q.Parameters.Add("UserName", userName);

            if (range.StartDate.HasValue)
            {
                q.Append(x => x.Where, "c.\"CreationDate\" >= @StartDate");
                //Bug in Dapper this line looses TimeZome info see: https://github.com/npgsql/npgsql/issues/972#issuecomment-218745473
                //q.Parameters.Add("StartDate", range.StartDate.Value);
                q.Parameters.AddDynamicParams(new { StartDate = range.StartDate.Value });
            }

            if (range.EndDate.HasValue)
            {
                q.Append(x => x.Where, "c.\"CreationDate\" <= @EndDate");
                //Bug in Dapper this line looses TimeZome info see: https://github.com/npgsql/npgsql/issues/972#issuecomment-218745473
                //q.Parameters.Add("EndDate", range.EndDate.Value);
                q.Parameters.AddDynamicParams(new { EndDate = range.EndDate.Value });
            }

            if (isDeleted.HasValue)
            {
                q.Append(x => x.Where, "\"IsDeleted\" = @IsDeleted");
                q.Parameters.Add("IsDeleted", isDeleted.Value);
            }

            if (!String.IsNullOrEmpty(subverse))
            {
                q.Append(x => x.Select, $" INNER JOIN {SqlFormatter.Table("Submission", "s", null, "NOLOCK")} ON c.\"SubmissionID\" = s.\"ID\"");
                q.Append(x => x.Where, "s.\"Subverse\" = @Subverse");
                q.Parameters.Add("Subverse", subverse);
            }

            var count = _db.Connection.ExecuteScalar <int>(q.ToString(), q.Parameters);

            return(count);
        }
        public async Task <CommandResponse <Comment> > DistinguishComment(int commentID)
        {
            DemandAuthentication();
            var response = CommandResponse.FromStatus <Comment>(null, Status.Invalid);
            var comment  = await this.GetComment(commentID);

            if (comment != null)
            {
                // check to see if request came from comment author
                if (User.Identity.Name == comment.UserName)
                {
                    // check to see if comment author is also sub mod or sub admin for comment sub
                    if (ModeratorPermission.HasPermission(User, comment.Subverse, ModeratorAction.DistinguishContent))
                    {
                        var m = new DapperMulti();

                        var u = new DapperUpdate();
                        //u.Update = $"{SqlFormatter.Table("Comment")} SET \"IsDistinguished\" = {SqlFormatter.ToggleBoolean("\"IsDistinguished\"")}";
                        u.Update = SqlFormatter.UpdateSetBlock($"\"IsDistinguished\" = {SqlFormatter.ToggleBoolean("\"IsDistinguished\"")}", SqlFormatter.Table("Comment"));
                        u.Where  = "\"ID\" = @id";
                        u.Parameters.Add("id", commentID);
                        m.Add(u);

                        var s = new DapperQuery();
                        s.Select = $"\"IsDistinguished\" FROM {SqlFormatter.Table("Comment")}";
                        s.Where  = "\"ID\" = @id";
                        m.Add(s);

                        //ProTip: The actual execution of code is important.
                        var result = await _db.Connection.ExecuteScalarAsync <bool>(m.ToCommandDefinition());

                        comment.IsDistinguished = result;

                        response = CommandResponse.FromStatus(comment, Status.Success);
                    }
                    else
                    {
                        response.Message = "User does not have permissions to distinquish content";
                        response.Status  = Status.Denied;
                    }
                }
                else
                {
                    response.Message = "User can only distinquish owned content";
                    response.Status  = Status.Denied;
                }
            }
            else
            {
                response.Message = "Comment can not be found";
                response.Status  = Status.Denied;
            }
            return(response);
        }
Beispiel #4
0
        public async Task <IEnumerable <SubverseSet> > GetUserSets(string userName)
        {
            var q = new DapperQuery();

            q.Select = $"SELECT * FROM {SqlFormatter.Table("SubverseSet", "subSet")}";
            q.Where  = "subSet.\"UserName\" = @UserName";
            q.Parameters.Add("UserName", userName);

            if (!userName.IsEqual(UserName))
            {
                q.Append(x => x.Where, $"subSet.\"IsPublic\" = {SqlFormatter.BooleanLiteral(true)}");
            }

            using (var db = new VoatDataContext())
            {
                var set = await db.Connection.QueryAsync <SubverseSet>(q.ToString(), q.Parameters);

                return(set);
            }
        }
Beispiel #5
0
        public async Task <IEnumerable <SubverseSubscriptionDetail> > GetSetListDescription(int setID, int?page = 0)
        {
            int count = 50;

            var q = new DapperQuery();

            q.Select     = $"s.\"ID\", s.\"Name\", s.\"Title\", s.\"Description\", s.\"CreationDate\", {SqlFormatter.As(SqlFormatter.IsNull("s.\"SubscriberCount\"", "0"), "\"SubscriberCount\"")}, sl.\"CreationDate\" AS \"SubscriptionDate\" FROM {SqlFormatter.Table("SubverseSetList", "sl")} INNER JOIN {SqlFormatter.Table("Subverse", "s")} ON (sl.\"SubverseID\" = s.\"ID\")";
            q.Where      = "sl.\"SubverseSetID\" = @ID";
            q.Parameters = new DynamicParameters(new { ID = setID });

            if (page.HasValue)
            {
                q.SkipCount = page.Value * count;
                q.TakeCount = count;
            }

            q.OrderBy = "s.\"Name\" ASC";
            using (var db = new VoatDataContext())
            {
                var result = await db.Connection.QueryAsync <SubverseSubscriptionDetail>(q.ToString(), q.Parameters);

                return(result);
            }
        }
        public Score UserContributionPoints(string userName, ContentType contentType, string subverse = null, bool isReceived = true, TimeSpan?timeSpan = null, DateTime?cutOffDate = null)
        {
            Func <IEnumerable <dynamic>, Score> processRecords = new Func <IEnumerable <dynamic>, Score>(records =>
            {
                Score score = new Score();
                if (records != null && records.Any())
                {
                    foreach (var record in records)
                    {
                        if (record.VoteStatus == 1)
                        {
                            score.UpCount = isReceived ? (int)record.VoteValue : (int)record.VoteCount;
                        }
                        else if (record.VoteStatus == -1)
                        {
                            score.DownCount = isReceived ? (int)record.VoteValue : (int)record.VoteCount;
                        }
                    }
                }
                return(score);
            });

            var groupingClause = $"SELECT \"UserName\", \"IsReceived\", \"ContentType\", \"VoteStatus\", SUM(\"VoteCount\") AS \"VoteCount\", SUM(\"VoteValue\") AS \"VoteValue\" FROM ({"{0}"}) AS a GROUP BY a.\"UserName\", a.\"IsReceived\", a.\"ContentType\", a.\"VoteStatus\"";

            var      archivedPointsClause = $"SELECT \"UserName\", \"IsReceived\", \"ContentType\", \"VoteStatus\", \"VoteCount\", \"VoteValue\" FROM {SqlFormatter.Table("UserContribution", "uc", null, "NOLOCK")} WHERE uc.\"UserName\" = @UserName AND uc.\"IsReceived\" = @IsReceived AND uc.\"ContentType\" = @ContentType UNION ALL ";
            var      alias     = "";
            DateTime?dateRange = timeSpan.HasValue ? CurrentDate.Subtract(timeSpan.Value) : (DateTime?)null;
            Score    s         = new Score();

            using (var db = new VoatDataContext())
            {
                var contentTypes = contentType.GetEnumFlags();
                foreach (var contentTypeToQuery in contentTypes)
                {
                    var q = new DapperQuery();

                    switch (contentTypeToQuery)
                    {
                    case ContentType.Comment:

                        //basic point calc query
                        q.Select  = $"SELECT @UserName AS \"UserName\", @IsReceived AS \"IsReceived\", @ContentType AS \"ContentType\", v.\"VoteStatus\" AS \"VoteStatus\", 1 AS \"VoteCount\", ABS(v.\"VoteValue\") AS \"VoteValue\" FROM {SqlFormatter.Table("CommentVoteTracker", "v", null, "NOLOCK")} ";
                        q.Select += $"INNER JOIN {SqlFormatter.Table("Comment", "c", null, "NOLOCK")} ON c.\"ID\" = v.\"CommentID\" INNER JOIN {SqlFormatter.Table("Submission", "s", null, "NOLOCK")} ON s.\"ID\" = c.\"SubmissionID\"";

                        //This controls whether we search for given or received votes
                        alias = (isReceived ? "c" : "v");
                        q.Append(x => x.Where, $"{alias}.\"UserName\" = @UserName");

                        break;

                    case ContentType.Submission:
                        //basic point calc query
                        q.Select = $"SELECT @UserName AS \"UserName\", @IsReceived AS \"IsReceived\", @ContentType AS \"ContentType\", v.\"VoteStatus\" AS \"VoteStatus\", 1 AS \"VoteCount\", ABS(v.\"VoteValue\") AS \"VoteValue\" FROM {SqlFormatter.Table("SubmissionVoteTracker", "v", null, "NOLOCK")} INNER JOIN {SqlFormatter.Table("Submission", "s", null, "NOLOCK")} ON s.\"ID\" = v.\"SubmissionID\"";

                        //This controls whether we search for given or received votes
                        alias = (isReceived ? "s" : "v");
                        q.Append(x => x.Where, $"{alias}.\"UserName\" = @UserName");

                        break;

                    default:
                        throw new NotImplementedException($"Type {contentType.ToString()} is not supported");
                    }

                    //if subverse/daterange calc we do not use archived table
                    if (!String.IsNullOrEmpty(subverse) || dateRange.HasValue)
                    {
                        if (!String.IsNullOrEmpty(subverse))
                        {
                            q.Append(x => x.Where, "s.\"Subverse\" = @Subverse");
                        }
                        if (dateRange.HasValue)
                        {
                            q.Append(x => x.Where, "v.\"CreationDate\" >= @DateRange");
                        }
                    }
                    else
                    {
                        q.Select = archivedPointsClause + q.Select;
                        q.Append(x => x.Where, "s.\"ArchiveDate\" IS NULL");
                    }

                    string statement = String.Format(groupingClause, q.ToString());
                    System.Diagnostics.Debug.WriteLine("Query Output");
                    System.Diagnostics.Debug.WriteLine(statement);
                    var records = db.Connection.Query(statement, new
                    {
                        UserName    = userName,
                        IsReceived  = isReceived,
                        Subverse    = subverse,
                        ContentType = (int)contentType,
                        DateRange   = dateRange
                    });
                    Score result = processRecords(records);
                    s.Combine(result);
                }
            }
            return(s);
        }
        public async Task <IEnumerable <Domain.Models.Vote> > GetVotes(string subverse, SearchOptions searchOptions)
        {
            IEnumerable <Data.Models.Vote> votes = null;
            //IEnumerable<Data.Models.VoteOption> options = null;
            //IEnumerable<Data.Models.VoteOutcome> outcomes = null;
            //IEnumerable<Data.Models.VoteRestriction> restrictions = null;

            var q = new DapperQuery();

            q.Select = $"SELECT {SqlFormatter.QuoteIndentifier("ID")} FROM {SqlFormatter.Table("Vote")}";
            if (!String.IsNullOrEmpty(subverse))
            {
                q.Where = "\"Subverse\" = @Subverse";
                q.Parameters.Add("Subverse", subverse);
            }
            q.OrderBy   = SqlFormatter.OrderBy("CreationDate", true);
            q.TakeCount = searchOptions.Count;
            q.SkipCount = searchOptions.Index;

            var voteIDs = await _db.Connection.QueryAsync <int>(q.ToString(), q.Parameters);

            if (voteIDs != null && voteIDs.Any())
            {
                //var voteIDs = votes.Select(x => x.ID).ToList();

                var data = await GetVoteDataModel(voteIDs.ToArray());

                votes = data;

                //q = new DapperQuery();
                //q.Select = $"SELECT * FROM {SqlFormatter.Table("VoteRestriction")} WHERE \"VoteID\" {SqlFormatter.In("@VoteID")} ";
                //q.Select += $"SELECT * FROM {SqlFormatter.Table("VoteOption")} WHERE \"VoteID\" {SqlFormatter.In("@VoteID")} ";
                //q.Parameters.Add("VoteID", voteIDs);

                //using (var multi = await _db.Connection.QueryMultipleAsync(q.ToString(), q.Parameters))
                //{
                //    restrictions = multi.Read<Data.Models.VoteRestriction>().ToList();
                //    options = multi.Read<Data.Models.VoteOption>().ToList();
                //}

                //var voteOptionIDs = options.Select(x => x.ID).ToList();
                //q = new DapperQuery();
                //q.Select = $"SELECT * FROM {SqlFormatter.Table("VoteOutcome")} WHERE \"VoteOptionID\" {SqlFormatter.In("@VoteOptionID")} ";
                //q.Parameters.Add("VoteOptionID", voteIDs);

                //outcomes = await _db.Connection.QueryAsync<Data.Models.VoteOutcome>(q.ToString(), q.Parameters);
            }

            ////CONSTRUCT
            //votes.ForEach(v => {
            //    v.VoteRestrictions = new List<VoteRestriction>();
            //    v.VoteRestrictions.AddRange(restrictions.Where(r => r.VoteID == v.ID).ToList());

            //    v.VoteOptions = new List<Models.VoteOption>();
            //    v.VoteOptions.AddRange(options.Where(o => o.VoteID == v.ID).ToList());

            //    v.VoteOptions.ForEach(option => {
            //        option.VoteOutcomes = new List<VoteOutcome>();
            //        var outcomeList = outcomes.Where(o => option.ID == o.VoteOptionID).ToList();
            //        option.VoteOutcomes.AddRange(outcomeList);
            //    });
            //});

            var domainVotes = votes.IsNullOrEmpty() ?
                              Enumerable.Empty <Domain.Models.Vote>() :
                              votes.Select(x => VoteDomainMaps.Map(x)).ToList();

            return(domainVotes);
        }
Beispiel #8
0
        public async Task <CommandResponse <Domain.Models.Set> > CreateOrUpdateSet(Set set)
        {
            DemandAuthentication();

            set.Name = set.Name.TrimSafe();
            //Evaulate Rules
            var context = new VoatRuleContext(User);

            context.PropertyBag.Set = set;
            var outcome = VoatRulesEngine.Instance.EvaluateRuleSet(context, RuleScope.CreateSet);

            if (!outcome.IsAllowed)
            {
                return(MapRuleOutCome <Set>(outcome, null));
            }

            var existingSet = _db.SubverseSet.FirstOrDefault(x => x.ID == set.ID);

            if (existingSet != null)
            {
                var perms = SetPermission.GetPermissions(existingSet.Map(), User.Identity);

                if (!perms.EditProperties)
                {
                    return(CommandResponse.FromStatus <Set>(null, Status.Denied, "User does not have permission to edit this set"));
                }

                //HACK: Need to clear this entry out of cache if name changes and check name
                if (!existingSet.Name.IsEqual(set.Name))
                {
                    if (_db.SubverseSet.Any(x => x.Name.ToLower() == set.Name.ToLower() && x.UserName.ToLower() == UserName.ToLower()))
                    {
                        return(CommandResponse.FromStatus <Set>(null, Status.Denied, "A set with this name already exists"));
                    }
                    CacheHandler.Instance.Remove(CachingKey.Set(existingSet.Name, existingSet.UserName));
                }

                existingSet.Name        = set.Name;
                existingSet.Title       = set.Title;
                existingSet.Description = set.Description;
                existingSet.IsPublic    = set.IsPublic;

                await _db.SaveChangesAsync().ConfigureAwait(CONSTANTS.AWAIT_CAPTURE_CONTEXT);

                return(CommandResponse.FromStatus <Set>(existingSet.Map(), Status.Success));
            }
            else
            {
                //Validation - MOVE TO RULES SYSTEM MAYBE
                if (!VoatSettings.Instance.SetCreationEnabled || VoatSettings.Instance.MaximumOwnedSets <= 0)
                {
                    return(CommandResponse.FromStatus <Set>(null, Status.Denied, "Set creation is currently disabled"));
                }

                if (VoatSettings.Instance.MaximumOwnedSets > 0)
                {
                    var d = new DapperQuery();
                    d.Select = $"SELECT COUNT(*) FROM {SqlFormatter.Table("SubverseSet", "subSet")}";
                    d.Where  = "subSet.\"Type\" = @Type AND subSet.\"UserName\" = @UserName";
                    d.Parameters.Add("Type", (int)SetType.Normal);
                    d.Parameters.Add("UserName", UserName);

                    var setCount = _db.Connection.ExecuteScalar <int>(d.ToString(), d.Parameters);
                    if (setCount >= VoatSettings.Instance.MaximumOwnedSets)
                    {
                        return(CommandResponse.FromStatus <Set>(null, Status.Denied, $"Sorry, Users are limited to {VoatSettings.Instance.MaximumOwnedSets} sets and you currently have {setCount}"));
                    }
                }


                //Create new set
                try
                {
                    var setCheck = GetSet(set.Name, UserName);
                    if (setCheck != null)
                    {
                        return(CommandResponse.FromStatus <Set>(null, Status.Denied, "A set with same name and owner already exists"));
                    }

                    var newSet = new SubverseSet
                    {
                        Name            = set.Name,
                        Title           = set.Title,
                        Description     = set.Description,
                        UserName        = UserName,
                        Type            = (int)SetType.Normal,
                        IsPublic        = set.IsPublic,
                        CreationDate    = Repository.CurrentDate,
                        SubscriberCount = 1, //Owner is a subscriber. Reminds me of that hair club commercial: I"m not only the Set Owner, I'm also a subscriber.
                    };

                    _db.SubverseSet.Add(newSet);
                    await _db.SaveChangesAsync().ConfigureAwait(CONSTANTS.AWAIT_CAPTURE_CONTEXT);

                    _db.SubverseSetSubscription.Add(new SubverseSetSubscription()
                    {
                        SubverseSetID = newSet.ID, UserName = UserName, CreationDate = CurrentDate
                    });
                    await _db.SaveChangesAsync().ConfigureAwait(CONSTANTS.AWAIT_CAPTURE_CONTEXT);

                    return(CommandResponse.Successful(newSet.Map()));
                }
                catch (Exception ex)
                {
                    return(CommandResponse.Error <CommandResponse <Set> >(ex));
                }
            }
        }
Beispiel #9
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);
        }