public async Task<Note> AddNoteForUser( Note anote ) {
//anote.AppliesToUsername = anote.AppliesToUsername.ToLower();
using ( SqlConnection conn = new SqlConnection( connstring ) ) {
string query = "insert into Notes(NoteTypeID,SubredditID,Submitter,Message,AppliesToUsername, n.Url, n.Timestamp) "
+ " values (@NoteTypeID,(select SubredditID from Subreddits where SubName = @SubName),@Submitter,@Message,@AppliesToUsername, @Url, @Timestamp);"
+ " select n.NoteID, n.NoteTypeID, s.SubName, n.Submitter, n.Message, n.AppliesToUsername, n.Url, n.Timestamp "
+ " from Notes n inner join Subreddits s on s.SubredditID = n.SubredditID "
+ " where n.NoteID = cast(SCOPE_IDENTITY() as int) ";
Note insertedNote = ( await conn.QueryAsync<Note>( query, new { anote.NoteTypeID, anote.SubName, anote.Submitter, anote.Message, anote.AppliesToUsername, anote.Url, anote.Timestamp } ) ).Single();
return insertedNote;
}
}
public async Task<Note> AddNoteToCabal(Note anote, string cabalSub ) {
using ( SqlConnection conn = new SqlConnection( connstring ) ) {
string query = @"
INSERT INTO Notes(NoteTypeID, SubredditID, Submitter, Message, AppliesToUsername, Url, Timestamp, ParentSubreddit )
OUTPUT INSERTED.NoteID, INSERTED.NoteTypeID, @CabalSub as 'SubName', INSERTED.Submitter, INSERTED.Message, INSERTED.AppliesToUsername, INSERTED.Url, INSERTED.Timestamp, INSERTED.ParentSubreddit
SELECT
@NoteTypeID,
(SELECT SubredditID from Subreddits where SubName = @CabalSub),
@Submitter,
n.Message,
n.AppliesToUsername,
n.Url,
@Timestamp,
@OriginalSubreddit
FROM Notes n
INNER JOIN Subreddits s on s.SubredditID = n.SubredditID
WHERE
n.NoteID = @NoteID
AND s.SubName like @OriginalSubreddit
";
Note insertedNote = ( await conn.QueryAsync<Note>( query, new { anote.NoteID, anote.NoteTypeID, CabalSub = cabalSub, anote.Submitter, anote.Timestamp, OriginalSubreddit = anote.SubName } ) ).Single();
return insertedNote;
}
}
/// <summary>
/// Returns True if user has no more notes in subreddit
/// </summary>
/// <param name="anote"></param>
/// <param name="uname"></param>
/// <returns></returns>
public async Task<bool> DeleteNoteForUser( Note anote, string uname ) {
using ( SqlConnection conn = new SqlConnection( connstring ) ) {
string query = @"
delete n
OUTPUT GETUTCDATE() as 'HistTimestamp','D' as 'HistAction',@uname as 'HistUser', DELETED.NoteID, DELETED.NoteTypeID, DELETED.SubRedditID,DELETED.Submitter,DELETED.Message,DELETED.AppliesToUsername,DELETED.URL,DELETED.TimeStamp,DELETED.ParentSubreddit into Notes_History
from Notes n INNER JOIN Subreddits sr on n.SubredditID = sr.SubredditID
where NoteID = @noteid and sr.SubName = @subname;
Select count(*)
from Notes n INNER JOIN Subreddits sr on n.SubredditID = sr.SubredditID
WHERE
n.AppliesToUsername = @AppliesToUsername and sr.SubName = @subname;
";
int count = (await conn.QueryAsync<int>( query, new { anote.NoteID, anote.SubName, uname, anote.AppliesToUsername } )).FirstOrDefault();
return count == 0;
}
}