仪表盘界面接口实现

3 files changed, 53 insertions, 16 deletions

diff --git a/server/apps/sysinfo.py b/server/apps/sysinfo.py
index 3cab7fd..053760c 100644
--- a/server/apps/sysinfo.py
+++ b/server/apps/sysinfo.py
@@ -1,13 +1,15 @@
 # 系统信息接口
 import datetime
 
-# 测试用
-from apps.util import fake
-
 from apiflask import APIBlueprint, Schema
 from apiflask.fields import Date, Integer, Nested, Dict, DateTime, String, List
 from apiflask.validators import OneOf
 
+# 测试用
+from apps.util import fake
+from settings import START_DAY
+from util import da
+
 bp = APIBlueprint("仪表盘接口", __name__, url_prefix="/sys")
 
 
@@ -38,16 +40,16 @@ class LogOut(Schema):
 def systate():
     # TODO:从数据库读取信息并统计
     # 已部署代理节点数量
-    agent_num = fake.random.randint(10, 100)
+    agent_num = da.count_data("agent")
     # 已执行任务数量（包括执行完毕和正在执行）
-    task_num = fake.random.randint(10, 100)
+    task_num = da.count_data("task")
     # 系统已运行天数
-    workday = fake.random.randint(10, 100)
+    workday = datetime.date.today() - START_DAY
     # 已探测目标统计
-    v6dns_num = fake.random.randint(10, 100)
-    dnssec_num = fake.random.randint(10, 100)
-    doh_num = fake.random.randint(10, 100)
-    dot_num = fake.random.randint(10, 100)
+    v6dns_num = da.count_data("target", {"IPv6": True})
+    dnssec_num = da.count_data("target", {"DNSSEC": True})
+    doh_num = da.count_data("target", {"DOH": True})
+    dot_num = da.count_data("target", {"DOT": True})
     # 返回结果
     return {
         "agent_num": agent_num,
@@ -73,12 +75,11 @@ def target_date():
     day = 1
     while day < 8:
         d = today - datetime.timedelta(day)
-        # TODO:从数据库读取并统计
         dates[d] = {
-            "v6dns": fake.random.randint(10, 20) * day,
-            "dnssec": fake.random.randint(10, 20) * day,
-            "doh": fake.random.randint(10, 20) * day,
-            "dot": fake.random.randint(10, 20) * day
+            "v6dns": da.count_data_by_time("target", time=d, search={"IPv6": True}),
+            "dnssec": da.count_data_by_time("target", time=d, search={"DNSSEC": True}),
+            "doh": da.count_data_by_time("target", time=d, search={"DOH": True}),
+            "dot": da.count_data_by_time("target", time=d, search={"DOT": True})
         }
         day += 1
     return {"date_data": dates}
diff --git a/server/apps/util.py b/server/apps/util.py
index ad95002..0b8604b 100644
--- a/server/apps/util.py
+++ b/server/apps/util.py
@@ -18,7 +18,16 @@ class DataHandler:
     tabmapping = {
         "agent": MYSQL_TAB_AGENT,
         "task": MYSQL_TAB_TASK,
-        "user": MYSQL_TAB_USER}
+        "user": MYSQL_TAB_USER,
+        "target": MYSQL_TAB_TARGETDATA}
+
+    # 参数与数据表中时间字段的映射关系
+    timemapping = {
+        "agent": "START_TIME",
+        "task": "CREATED_TIME",
+        "user": "CREATED_TIME",
+        "target": "UPDATED_TIME",
+    }
 
     # 数据库链接及数据库初始化
     def __init__(self):
@@ -193,5 +202,29 @@ class DataHandler:
             self.cursor.execute(sql)
             return self.cursor.fetchall()
 
+    # 统计符合条件的信息数量
+    def count_data(self, data_type="agent", search=None):
+        # 参数映射到表名
+        tabname = self.tabmapping[data_type]
+        if search is None:
+            sql = """SELECT count(*) FROM %s""" % (tabname)
+            self.cursor.execute(sql)
+            return dict(self.cursor.fetchall()[0]).popitem()[1]
+        else:
+            search = dict(search)
+            key, value = search.popitem()
+            sql = """SELECT count(*) FROM %s WHERE %s=%s""" % (tabname, key, value)
+            self.cursor.execute(sql)
+            return dict(self.cursor.fetchall()[0]).popitem()[1]
+
+    def count_data_by_time(self, data_type="agent", time=None, search=None):
+        tabname = self.tabmapping[data_type]
+        timename = self.timemapping[data_type]
+        key, value = search.popitem()
+        sql = """SELECT COUNT(*) FROM %s WHERE
+         DATE_FORMAT(%s,'%Y-%m-%d')=%s AND %s=%s""" % (tabname, timename, time, key, value)
+        self.cursor.execute(sql)
+        return dict(self.cursor.fetchall()[0]).popitem()[1]
+
 
 da = DataHandler()
diff --git a/server/settings.py b/server/settings.py
index 4791c0f..a67923f 100644
--- a/server/settings.py
+++ b/server/settings.py
@@ -1,5 +1,8 @@
+import datetime
+
 DEBUG = True
 SERVER_PORT = 12526
+START_DAY = datetime.date.today()
 
 # 数据库配置
 MYSQL_HOST = '127.0.0.1'