From adc88dbdf3274d4d0cf15b5f2cf7b0bbb939bfe0 Mon Sep 17 00:00:00 2001 From: Pinapelz Date: Sat, 18 Nov 2023 21:21:22 -0800 Subject: v3: re-write frontend using next js --- backend/app.py | 20 +++++++++++++++++--- backend/data/last_refresh.txt | 2 +- backend/graph.py | 30 ++++++++++++++++++++++++++++++ backend/index.html | 14 ++++++++++++++ backend/nijitrack.py | 19 +++++++++++++++---- backend/sql_table_config.json | 4 ++-- 6 files changed, 79 insertions(+), 10 deletions(-) create mode 100644 backend/graph.py create mode 100644 backend/index.html (limited to 'backend') diff --git a/backend/app.py b/backend/app.py index 51b0dbb..71ed62a 100644 --- a/backend/app.py +++ b/backend/app.py @@ -3,14 +3,28 @@ Flask app for serving the static files """ from flask import Flask, send_file, send_from_directory, jsonify, abort from flask_cors import CORS +from sql.sql_handler import SQLHandler +import fileutil as fs +import datetime app = Flask(__name__) +CONFIG = fs.load_config("config.ini") CORS(app) -@app.route('/') -def main_page(): - return "We are offline at the moment" +@app.route("/") +def index(): + return send_file("index.html") + +@app.route("/api/subscribers") +def api_subscribers(): + server = SQLHandler(CONFIG["SQL"]["host"], CONFIG["SQL"]["user"], CONFIG["SQL"]["password"], CONFIG["SQL"]["database"]) + data = server.execute_query("SELECT * FROM subscriber_data INNER JOIN 24h_historical ON subscriber_data.channel_id = 24h_historical.channel_id ORDER BY subscriber_count DESC") + channel_data_list = [{"channel_name":row[3], "profile_pic": row[2], "subscribers": row[4], "sub_org": row[5], "video_count": row[6], "day_diff": int(row[10] - int(row[4]))} for row in data] + subscriber_data = {"timestamp": datetime.datetime.now(),"channel_data":channel_data_list} + + return jsonify(subscriber_data) + @app.errorhandler(404) diff --git a/backend/data/last_refresh.txt b/backend/data/last_refresh.txt index ae97356..07602c3 100644 --- a/backend/data/last_refresh.txt +++ b/backend/data/last_refresh.txt @@ -1 +1 @@ -2023-11-17 \ No newline at end of file +2023-11-18 \ No newline at end of file diff --git a/backend/graph.py b/backend/graph.py new file mode 100644 index 0000000..16c6f6e --- /dev/null +++ b/backend/graph.py @@ -0,0 +1,30 @@ +import plotly.graph_objs as go +import pandas as pd +import warnings +import math +from datetime import datetime, timedelta +import numpy as np + +def plot_subscriber_count_over_time(server, table_name, gtitle = "Subscriber Count Over Time for Nijisanji Members", + overrideQuery = None, markers = "lines", exclude_channels = []): + warnings.filterwarnings('ignore') # Ignore pandas warning regarding pyodbc + query = f"SELECT name, subscriber_count, timestamp, channel_id FROM {table_name} ORDER by timestamp DESC" if overrideQuery is None else overrideQuery + df = pd.read_sql_query(query, server.get_connection()) + groups = df.groupby("name") + fig = go.Figure() + config = dict({'responsive': True, 'displaylogo': False, 'modeBarButtonsToAdd': ['pan2d', 'zoomIn2d', 'zoomOut2d']}) + for channel, group in groups: + if len(exclude_channels) != 0 and group['channel_id'].iloc[0] in exclude_channels: + continue + fig.add_trace(go.Scattergl( + x = group["timestamp"], y = group["subscriber_count"], name = channel, mode = markers, + showlegend = True)) + fig.update_layout( + title = {'text': gtitle, 'x': 0.5, 'xanchor': 'center', + 'yanchor': 'top', 'font': {'family': 'Arial', 'size': 30}}, + xaxis_title = "Timestamp", + yaxis_title = "Subscribers", + legend = dict(font = dict(size = 16), title = dict(text = "Channels")), + height = 950, + ) + return fig.to_html(config = config) \ No newline at end of file diff --git a/backend/index.html b/backend/index.html new file mode 100644 index 0000000..3451ab5 --- /dev/null +++ b/backend/index.html @@ -0,0 +1,14 @@ + + + +
+
+ + \ No newline at end of file diff --git a/backend/nijitrack.py b/backend/nijitrack.py index 30b31cc..8879e9c 100644 --- a/backend/nijitrack.py +++ b/backend/nijitrack.py @@ -1,10 +1,10 @@ -import os import time import fileutil as fs from sql.sql_handler import SQLHandler from webapi.holodex import HolodexAPI from webapi.youtube import YouTubeAPI +import graph from decorators import * import argparse @@ -43,10 +43,14 @@ def record_subscriber_data(data: list): pfp = channel["photo"] sub_count = channel["subscriber_count"] channel_name = channel["english_name"] + sub_org = channel["group"] + video_count = channel["video_count"] if channel_name is None: channel_name = channel["name"] + if sub_org is None: + sub_org = "Unknown" channel_name = transform_sql_string(channel_name) - data_tuple = (channel_id, pfp, channel_name, sub_count, time.strftime('%Y-%m-%d %H:%M:%S')) + data_tuple = (channel_id, pfp, channel_name, sub_count, sub_org, video_count, time.strftime('%Y-%m-%d %H:%M:%S')) server.insert_row(name = CONFIG["TABLES"]["live"], column = DATA_SETTING["LIVE_HEADER"], data=data_tuple) record_diff_data(data_tuple, refresh_daily) @@ -62,7 +66,11 @@ def holodex_generation(server: SQLHandler): holodex = HolodexAPI(CONFIG["API"]["holodex"]) for organization in holodex_organizations: holodex.set_organization(organization) - record_subscriber_data(holodex.get_subscriber_data()) + subscriber_data = holodex.get_subscriber_data() + record_subscriber_data(subscriber_data) + #for channel in subscriber_data: + # print(channel["name"] + " " + channel["group"] + " " + channel["video_count"] ) + #input() return holodex.get_generated_channel_data(), holodex.get_inactive_channels() @log("Running YouTube Generation") @@ -101,4 +109,7 @@ if __name__ == "__main__": inactive_channels = fs.get_excluded_channels() else: channel_data, inactive_channels = holodex_generation(server) - fs.update_excluded_channels(inactive_channels) \ No newline at end of file + fs.update_excluded_channels(inactive_channels) + graph_html = graph.plot_subscriber_count_over_time(server, CONFIG["TABLES"]["historical"], exclude_channels=combine_excluded_channel_ids(inactive_channels, fs.get_excluded_channels())) + with open("index.html", "w", encoding="utf-8") as file: + file.write(graph_html) \ No newline at end of file diff --git a/backend/sql_table_config.json b/backend/sql_table_config.json index 0e2e6e5..8477fbf 100644 --- a/backend/sql_table_config.json +++ b/backend/sql_table_config.json @@ -1,6 +1,6 @@ { - "LIVE_COLUMNS": "id INT PRIMARY KEY AUTO_INCREMENT, channel_id VARCHAR(255), profile_pic VARCHAR(255), name VARCHAR(255), subscriber_count INT, timestamp DATETIME", - "LIVE_HEADER": "channel_id, profile_pic, name, subscriber_count, timestamp", + "LIVE_COLUMNS": "id INT PRIMARY KEY AUTO_INCREMENT, channel_id VARCHAR(255), profile_pic VARCHAR(255), name VARCHAR(255), subscriber_count INT, suborg VARCHAR(255), video_count INT, timestamp DATETIME", + "LIVE_HEADER": "channel_id, profile_pic, name, subscriber_count, suborg, video_count, timestamp", "DAILY_COLUMNS": "id INT PRIMARY KEY AUTO_INCREMENT, channel_id VARCHAR(255), sub_diff INT", "DAILY_HEADER": "channel_id, sub_diff", "HOLODEX_ORGS": "Nijisanji" -- cgit v1.2.3