public async Task <List <NewsHeader> > GetUnreadNewsAsync(
string channelId,
string personId,
UpdatedNews updatedNews,
SquashUpdate squashUpdate)
{
var hideUpdatesQuery = " and n.OriginalPostId is null";
var empty = string.Empty;
var query = " select n.Id, n.Title, n.Published, n.Published, n.Link, n.OriginalPostId" +
" from News n" +
" where n.Id not in(" +
" select cnr.NewsId" +
" from ChannelNewsReads cnr" +
" where cnr.ChannelId = @channelId" +
" and cnr.PersonId = @personId)" +
" and n.ChannelId = @channelId" +
(updatedNews == UpdatedNews.Ignore ? hideUpdatesQuery : empty) +
" order by n.Published desc";
if (updatedNews == UpdatedNews.Show && squashUpdate == SquashUpdate.Squash)
{
query = " select t.Id, t.Title, t.Published, t.Link, t.OriginalPostId, rn" +
" into #ns" +
" from ( " +
" select n.Id, n.Title, n.Published, n.Link, n.OriginalPostId, ROW_NUMBER() over (PARTITION by link,OriginalPostId order by published desc) as rn" +
" from News n" +
" where n.Id not in" +
" ( select cnr.NewsId from ChannelNewsReads cnr" +
" where cnr.ChannelId = @channelId and cnr.PersonId = @personId)" +
" and n.ChannelId = @channelId" +
" )t " +
" where rn=1" +
" select l.* from #ns l" +
" left join #ns r on l.OriginalPostId = r.Id" +
" where r.Id is null";
}
var queryData = new
{
channelId,
personId
};
using (var connection = new SqlConnection(this.connection))
{
var news = await connection.QueryAsync <NewsHeader>(query, queryData);
return(news.ToList());
}
}
public async Task UpdateSquashNewsUpdates(Guid userId, SquashUpdate squashNewsUpdates)
{
var sqlQuery = "UPDATE ReadersSettings SET SquashNewsUpdates = @squashNewsUpdates WHERE PersonId = @userId";
var sqlData = new
{
squashNewsUpdates = squashNewsUpdates.ToString(),
userId
};
await using var connection = new SqlConnection(this.connectionString);
await connection.ExecuteAsync(sqlQuery, sqlData);
}
public UpdateSquashNewsUpdatesRequest(Guid userId, SquashUpdate squashNewsUpdates)
{
UserId = userId;
SquashNewsUpdates = squashNewsUpdates;
}