Login Register





C# - Find hot threads filter_list
Author
Message
C# - Find hot threads #1
I find the "Todays posts" feature inadequate for finding threads that get a lot of views (and thus are attractive to users). View count alone cannot be relied on because it also includes guest accounts. I set out to make a program to let me see at a quick glance which threads are viewed by real members on a rolling daily basis. Below is the source code for that program:

Code:
using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;

SqlConnection cn = new(ConfigurationManager.ConnectionStrings["db"].ConnectionString);
cn.Open();
RestSharp.RestClient client = new();
client.BaseUrl = new("https://sinister.ly");
RestSharp.RestRequest request;
for (int i = 1; i < 50; ++i)
{
    request = new($"/online.php{(i == 1 ? string.Empty : $"?page={i}")}");
    RestSharp.IRestResponse response = client.Execute(request);
    Regex rx = new("<tr>\n<td.*><a.*User-.*\">(.*)</span></a></td>\\n<td.*</td>\\n<td.*>.*Thread <a.*\"Thread-(.*)\">(.*)</a>.*\\n</tr>");
    MatchCollection matches = rx.Matches(response.Content);
    if (!matches.Any())
        break;
    foreach (Match match in matches)
    {
        string thread = match.Groups[3].Value
            .Replace("<strong>", "[")
            .Replace("</strong>", "]")
            .Replace("</span>", string.Empty)
            .Replace("&nbsp;", " ")
            .Replace("&quot;", "\"");
        if (thread.Contains("\">"))
            thread = thread[..thread.IndexOf("\">")];
        using SqlCommand cmd = new("ThreadActivityIns", cn)
        {
            CommandType = System.Data.CommandType.StoredProcedure
        };
        cmd.Parameters.AddWithValue("@ThreadId", match.Groups[2].Value);
        cmd.Parameters.AddWithValue("@ThreadName", thread);
        cmd.Parameters.AddWithValue("@Username", match.Groups[1].Value);
cmd.ExecuteNonQuery();
    }
}

/*
CREATE TABLE Threads(
ThreadId VARCHAR(50) NOT NULL PRIMARY KEY,
Name NVARCHAR(150) NOT NULL)
CREATE TABLE ThreadActivity(
Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
ThreadId VARCHAR(50) NOT NULL,
Username NVARCHAR(50) NOT NULL,
Accessed DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP)
GO
CREATE PROCEDURE ThreadActivityIns
@ThreadId VARCHAR(50),
@ThreadName NVARCHAR(150),
@Username NVARCHAR(50)
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT ThreadId FROM Threads WHERE ThreadId = @ThreadId)
INSERT INTO Threads (ThreadId, Name) VALUES(@ThreadId, @ThreadName)
SET NOCOUNT OFF
IF NOT EXISTS(SELECT Id
FROM ThreadActivity
WHERE Username = @Username
AND ThreadId = @ThreadId
AND DATEDIFF(DAY, Accessed, CURRENT_TIMESTAMP) = 0)
INSERT INTO ThreadActivity
(ThreadId, Username)
VALUES (@ThreadId, @Username)
VALUES (@ThreadId, @Username)
GO
CREATE PROCEDURE PopularThreads
AS
SELECT ThreadId, CNT
INTO #tmp
FROM (
  SELECT distinct
ThreadId
,ROW_NUMBER() OVER(PARTITION BY ThreadId ORDER BY ThreadId) CNT
FROM ThreadActivity
WHERE DATEDIFF(DAY, Accessed, CURRENT_TIMESTAMP) = 0) A
WHERE CNT > 1
ORDER BY CNT DESC
SELECT DISTINCT ThreadId FROM  #tmp
DROP TABLE #tmp
GO
*/

I also built up a website that will auto update with the list: https://sl.ipseitysoftware.com/threads.html

Unfortunately, the feature/tool will probably never be useful, per @Oni
[Image: p4refAM.png]

But hey, no sense in not sharing the concept code.

[+] 1 user Likes phyrrus9's post
Reply







Users browsing this thread: 1 Guest(s)