public static async Task <UserPostingHistory> GetUserPostingHistory(string userName)
{
using (var con = DirtBagConnection.GetConn()) {
var query = "" +
"Select up.ChannelID, up.ThingID " +
"FROM dirtbag.UserPosts up " +
//"left JOIN dirtbag.PostRemovals rem on up.PostID = rem.PostID " +
"where username like @UserName " +
"and up.ChannelID is not null " +
//"group by channelid";
"";
UserPostingHistory results = new UserPostingHistory();
results.UserName = userName;
results.PostingHistory = new Dictionary <string, List <string> >();
var vals = await con.QueryAsync(query, param : new { userName });
foreach (dynamic kv in vals)
{
if (!results.PostingHistory.ContainsKey(kv.ChannelID))
{
results.PostingHistory.Add(kv.ChannelID, new List <string>());
}
results.PostingHistory[kv.ChannelID].Add(kv.ThingID);
}
return(results);
}
}
static void Main( string[] args ) {
ActiveModules = new List<IModule>();
//Instantiate and throw away a Reddit instance so the static constructor won't interfere with the WebAgent later.
new Reddit();
var conn = new DirtBagConnection();
var sub = ConfigurationManager.AppSettings["Subreddit"];
conn.InitializeConnection( new[] { sub } );
Initialize();
string baseAddresses = System.Configuration.ConfigurationManager.AppSettings["ApiListeningUrls"];
baseAddresses += "," + string.Join(",",args);
if ( !string.IsNullOrWhiteSpace( baseAddresses ) ) {
var opts = new StartOptions();
foreach ( string address in baseAddresses.Split( ',' ) ) {
if ( !string.IsNullOrWhiteSpace( address ) ) {
opts.Urls.Add( address );
}
}
_app = WebApp.Start<Startup>( opts );
}
WaitHandle.WaitOne(); //Go the f**k to sleep
}
public static UserPost AddRemoval(PostRemoval removal)
{
using (var conn = DirtBagConnection.GetConn()) {
var query = "" +
//"declare @theUserPost Table( " +
//"[PostID] INTEGER, " +
////"[UserID] INTEGER NOT NULL, " +
//"[UserName] varchar(50), " +
//"[Link] varchar(200), " +
//"[ChannelID] varchar(100), " +
//"[ChannelName] varchar(200), " +
//"[Subreddit] varchar(100) ) " +
//"" +
"insert into UserPosts (UserName,ThingID,Link,PostTime,ChannelID,ChannelName,Subreddit) " +
"select @UserName, @ThingID, @Link, @PostTime, @ChannelID, @ChannelName, @Subreddit " +
"WHERE NOT EXISTS " +
"(select PostID from UserPosts where Link = @Link) " +
"; " +
//"insert into @theUserPostTable " +
//"select top 1 PostID, UserName, Link, ChannelID, ChannelName, Subreddit " +
//"FROM UserPosts " +
//"Where Link = @Link " +
"; " +
"insert into PostRemovals (TimeStamp,ModName,Reason,PostID) " +
"select @TimeStamp, @ModName, @Reason, PostID " +
"from UserPosts " +
"WHERE Link = @Link " +
"; " +
"select * from UserPosts where Link = @Link;";
var toReturn = conn.Query <UserPost>(query, new {
removal.Post.UserName,
removal.Post.ThingID,
removal.Post.Link,
removal.Post.PostTime,
removal.Post.ChannelID,
removal.Post.ChannelName,
removal.Post.Subreddit,
removal.TimeStamp,
removal.ModName,
removal.Reason
}).Single();
if (string.IsNullOrEmpty(toReturn.ChannelName))
{
return(toReturn);
}
return(null);
}
}
public static void UpdateProcessedPost(ProcessedPost post)
{
byte[] serialized = Helpers.ProcessedPostHelpers.SerializeAndCompressResults(post);
var query = "" +
"Update ProcessedPosts " +
"Set ActionID = (select ID from Actions where ActionName = @Action), AnalysisResults = @AnalysisResults, SeenByModules = @SeenByModules " +
//"FROM ProcessedPosts pp " +
//"inner join Actions act on act.ActionName = @Action " +
"where PostID like @PostID " +
";";
using (var conn = DirtBagConnection.GetConn()) {
conn.Execute(query, new { AnalysisResults = serialized, post.Action, post.PostID, post.SeenByModules });
}
}
public static void UpdatePost(UserPost post)
{
if (post.Link.Length > 200)
{
post.Link = post.Link.Substring(0, 200);
}
using (var conn = DirtBagConnection.GetConn()) {
var query = "" +
"update UserPosts " +
"set UserName = @UserName, ThingID = @ThingID, Link = @Link, PostTime = @PostTime, ChannelID = @ChannelID, " +
"ChannelName = @ChannelName, Subreddit = @Subreddit " +
"WHERE PostID = @PostID";
conn.Execute(query, new { post.UserName, post.ThingID, post.Link, post.PostTime, post.ChannelID, post.ChannelName, post.Subreddit, post.PostID });
}
}
public static void InsertPost(UserPost post)
{
if (post.Link.Length > 200)
{
post.Link = post.Link.Substring(0, 200);
}
using (var conn = DirtBagConnection.GetConn()) {
var query = "" +
"insert into UserPosts (UserName,ThingID,Link,PostTime,ChannelID,ChannelName,Subreddit) " +
"select @UserName, @ThingID, @Link, @PostTime, @ChannelID, @ChannelName, @Subreddit " +
"WHERE NOT EXISTS " +
"(select PostID from UserPosts where Link = @Link) " +
";";
conn.Execute(query, new { post.UserName, post.ThingID, post.Link, post.PostTime, post.ChannelID, post.ChannelName, post.Subreddit });
}
}
public static void AddProcessedPost(ProcessedPost post)
{
//string jsonAnalysisResults = JsonConvert.SerializeObject( post.AnalysisResults,Formatting.None );
byte[] serialized = Helpers.ProcessedPostHelpers.SerializeAndCompressResults(post); //System.Text.Encoding.ASCII.GetBytes( jsonAnalysisResults ); //This would need to change to support unicode report reasons & full explanations
var query = "" +
"insert into ProcessedPosts (SubredditID,PostID,ActionID,SeenByModules,AnalysisResults) " +
"select sub.ID, @PostID, act.ID, @SeenByModules, @AnalysisResults " +
"from Subreddits sub " +
"inner join Actions act on act.ActionName = @Action " +
"where sub.SubName like @SubName" +
";";
using (var conn = DirtBagConnection.GetConn()) {
conn.Execute(query, new { post.SubName, post.PostID, post.Action, post.SeenByModules, AnalysisResults = serialized });
}
}
public static UserAnalysis GetUserAnalysis(string UserName)
{
using (var con = DirtBagConnection.GetConn()) {
var query = "" +
"Select up.ChannelID,count( up.postID ), count( distinct rem.PostID ) " +
"FROM dirtbag.UserPosts up " +
"left JOIN dirtbag.PostRemovals rem on up.PostID = rem.PostID " +
"where username like @UserName " +
"group by channelid";
var analysis = new UserAnalysis();
analysis.UserName = UserName;
analysis.Posts = new List <UserPost>();
var posts = new Dictionary <int, UserPost>();
var result = con.Query <UserPost, PostRemoval, UserAnalysis>(query, (up, pr) => {
UserPost post;
if (!posts.ContainsKey(up.PostID))
{
up.Removals = new List <PostRemoval>();
posts.Add(up.PostID, up);
}
post = posts[up.PostID];
if (pr != null)
{
post.Removals.Add(pr);
}
return(analysis);
}, splitOn: "RemovalID", param: new { UserName });
analysis.Posts = posts.Values.ToList();
return(analysis);
}
}
public static List <ProcessedPost> GetProcessed(List <string> postIDs)
{
var tableParam = new DataTable();
tableParam.Columns.Add("postID", typeof(string));
foreach (string postID in postIDs)
{
tableParam.Rows.Add(postID);
}
var query = @"
select sub.SubName, p.PostID, act.ActionName as ""Action"",p.SeenByModules, p.AnalysisResults
from ProcessedPosts p
inner join Subreddits sub on sub.ID = p.SubredditID
inner join Actions act on act.ID = p.ActionID
inner join @postIDs pids on pids.PostID = p.PostID
;";
using (var conn = DirtBagConnection.GetConn()) {
return(conn.Query <ProcessedPost, byte[], ProcessedPost>(query, (pp, b) => {
pp.AnalysisResults = Helpers.ProcessedPostHelpers.InflateAndDeserializeResults(b);
return pp;
}, splitOn: "AnalysisResults", param: new { postIDs = tableParam.AsTableValuedParameter("postIDs") }).ToList());
}
}
public static DateTime?GetLastProcessedRemovalDate(string sub)
{
using (var conn = DirtBagConnection.GetConn()) {
var query = "" +
"select MAX(TimeStamp) " +
"FROM PostRemovals pr " +
"inner join UserPosts up on pr.PostID = up.PostID " +
"WHERE " +
"up.Subreddit = @sub ";
var time = conn.Query <DateTime?>(query, new { sub }).Single();
if (!time.HasValue)
{
return(null);
}
if (DirtBagConnection.UseLocalDB)
{
return(time.Value.ToUniversalTime());
}
else
{
return(time.Value);
}
}
}