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);
}
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);
}
}
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);
}
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));
}
}
}
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);
}