d8d-admin-mobile-starter-public/server/routes_big.ts

import { Hono } from "hono";
import debug from "debug";
import dayjs from "dayjs";
import utc from "dayjs/plugin/utc";
import type {
  RackInfo,
  CategoryChartData,
  OnlineRateChartData,
  StateChartData,
  AlarmChartData,
  DeviceWithAssetInfo,
  AlarmDeviceData,
} from "../client/share/monitorTypes.ts";

import {
  DeviceStatus,
  AssetTransferType,
  MetricType,
  NetworkStatus,
} from "../client/share/monitorTypes.ts";

import type { Variables, WithAuth } from "./middlewares.ts";
import { DeleteStatus } from "../client/share/types.ts";

dayjs.extend(utc);

const log = {
  api: debug("d8d:chart:api"),
  app: debug("d8d:chart:app"),
};

// 创建大屏路由
export function createBigRoutes(withAuth: WithAuth) {
  const bigRoutes = new Hono<{ Variables: Variables }>();

  // 资产分类图表数据
  bigRoutes.get("/zichan_category_chart", async (c) => {
    try {
      const apiClient = c.get('apiClient');

      // 直接使用查询参数而不是filters层
      const isDeleted = c.req.query("is_deleted") === "1" ? 1 : 0;

      // 执行SQL查询统计各分类设备数量
      const result = await apiClient.database.raw(
        `
          SELECT 
            CASE 
              WHEN zc.name IS NULL THEN '未分类' 
              ELSE zc.name 
            END as 设备分类, 
            COUNT(zi.id) as 设备数
          FROM 
            zichan_info zi
          LEFT JOIN 
            zichan_category zc ON zi.device_category = zc.id
          WHERE 
            zi.is_deleted = ?
          GROUP BY 
            zi.device_category
          ORDER BY 
            设备数 DESC
        `,
        [isDeleted]
      );

      return c.json(result as CategoryChartData[]);
    } catch (error) {
      console.error("获取设备分类统计失败:", error);
      return c.json([]);
    }
  });

  // 设备在线率变化数据
  bigRoutes.get("/zichan_online_rate_chart", async (c) => {
    try {
      const apiClient = c.get('apiClient');
      const query = c.req.query();

      // 直接使用扁平化查询参数
      const startTime =
        query.created_at_gte ||
        dayjs().subtract(7, "day").format("YYYY-MM-DD HH:mm:ss");
      const endTime =
        query.created_at_lte || dayjs().format("YYYY-MM-DD HH:mm:ss");
      const dimension = query.dimension || "day";

      // 根据维度确定日期格式
      let dateFormat = "%Y-%m-%d";
      if (dimension === "hour") {
        dateFormat = "%Y-%m-%d %H:00";
      } else if (dimension === "month") {
        dateFormat = "%Y-%m";
      }

      // 按日期聚合查询设备在线数量
      const onlineData = await apiClient.database.raw(
        `
          SELECT 
            DATE_FORMAT(created_at, '${dateFormat}') AS time_interval,
            COUNT(CASE WHEN network_status = ${NetworkStatus.CONNECTED} THEN 1 END) AS online_devices,
            COUNT(id) AS total_devices
          FROM 
            zichan_info
          WHERE 
            is_deleted = ${DeleteStatus.NOT_DELETED}
            AND created_at BETWEEN ? AND ?
          GROUP BY 
            DATE_FORMAT(created_at, '${dateFormat}')
          ORDER BY 
            time_interval ASC
        `,
        [startTime, endTime]
      );

      // 返回明确类型的响应数据
      return c.json(onlineData as OnlineRateChartData[]);
    } catch (error) {
      console.error("获取在线率变化数据失败:", error);
      return c.json([]);
    }
  });

  // 资产状态分布数据
  bigRoutes.get("/zichan_state_chart", async (c) => {
    try {
      const apiClient = c.get('apiClient');

      // 直接使用查询参数
      const isDeleted = c.req.query("is_deleted") === "1" ? 1 : 0;

      // 执行SQL查询统计各状态设备数量
      const result = await apiClient.database.raw(
        `
          SELECT 
            CASE 
              WHEN ztl.asset_transfer = ${AssetTransferType.STOCK} THEN '设备在库'
              WHEN ztl.asset_transfer = ${AssetTransferType.BORROW} THEN '设备借用'
              WHEN ztl.asset_transfer = ${AssetTransferType.RETURN} THEN '设备归还'
              WHEN ztl.asset_transfer = ${AssetTransferType.LOST} THEN '设备遗失'
              WHEN ztl.asset_transfer = ${AssetTransferType.MAINTAIN} THEN '设备维护保养'
              ELSE '其他'
            END as 资产流转,
            COUNT(ztl.id) as 设备数
          FROM 
            zichan_trans_log ztl
          LEFT JOIN 
            zichan_info zi ON ztl.asset_id = zi.id
          WHERE 
            ztl.is_deleted = ? AND zi.is_deleted = ?
          GROUP BY 
            ztl.asset_transfer
          ORDER BY 
            设备数 DESC
        `,
        [isDeleted, isDeleted]
      );

      return c.json(result as StateChartData[]);
    } catch (error) {
      console.error("获取资产状态分布数据失败:", error);
      return c.json([]);
    }
  });

  // 告警数据趋势
  bigRoutes.get("/zichan_alarm_chart", async (c) => {
    try {
      const apiClient = c.get('apiClient');
      const query = c.req.query();

      // 直接使用扁平化查询参数
      const startTime =
        query.created_at_gte ||
        dayjs()
          .utc()
          .subtract(1, "day")
          .startOf("day")
          .format("YYYY-MM-DD HH:mm:ss");
      const endTime =
        query.created_at_lte ||
        dayjs().utc().endOf("day").format("YYYY-MM-DD HH:mm:ss");
      const dimension = query.dimension || "hour";

      log.app("startTime", startTime);
      log.app("endTime", endTime);

      // 根据维度确定日期格式
      let dateFormat = "%H:00";
      let sortFormat = "%Y-%m-%d %H:00";
      if (dimension === "day") {
        dateFormat = "%Y-%m-%d";
        sortFormat = "%Y-%m-%d";
      } else if (dimension === "month") {
        dateFormat = "%Y-%m";
        sortFormat = "%Y-%m";
      }

      // 按日期聚合查询告警数量
      const alarmData = (await apiClient.database.raw(
        `
          SELECT 
            DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+08:00'), '${dateFormat}') AS time_interval,
            COUNT(id) AS total_devices,
            DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+08:00'), '${sortFormat}') AS sort_time
          FROM 
            device_alerts
          WHERE 
            is_deleted = ${DeleteStatus.NOT_DELETED}
            AND created_at BETWEEN ? AND ?
          GROUP BY 
            DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+08:00'), '${dateFormat}'),
            DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+08:00'), '${sortFormat}')
          ORDER BY 
            sort_time ASC
        `,
        [startTime, endTime]
      )) as AlarmChartData[];

      // 移除排序字段，只返回需要的数据
      const formattedData = alarmData.map((item) => ({
        time_interval: item.time_interval,
        total_devices: item.total_devices,
      }));

      return c.json(formattedData);
    } catch (error) {
      console.error("获取告警趋势数据失败:", error);
      return c.json([]);
    }
  });

  // 设备实例数据
  bigRoutes.get("/device-instances", async (c) => {
    try {
      const apiClient = c.get('apiClient');
      const query = c.req.query();

      // 直接使用查询参数
      const isDeleted = query.is_deleted === "1" ? 1 : 0;

      // 查询设备实例数据，关联资产信息和最新监控数据
      const deviceData = await apiClient.database.raw(
        `
          SELECT 
            di.id,
            di.address as ip_address,
            zi.asset_name,
            zi.device_category,
            zi.cpu,
            zi.memory,
            zi.disk,
            di.is_deleted
          FROM 
            device_instances di
          LEFT JOIN 
            zichan_info zi ON di.id = zi.id
          WHERE 
            di.is_deleted = ?
        `,
        [isDeleted]
      );

      const getMetricValue = async (deviceId: number, metricType: MetricType) => {
        const result = await apiClient.database
          .table("device_monitor_data")
          .where("device_id", deviceId)
          .where("metric_type", metricType)
          .where("is_deleted", DeleteStatus.NOT_DELETED)
          .first();
        return result?.status;
      };

      const promises = deviceData.map(async (item: DeviceWithAssetInfo) => {
        item.device_status =
          (await getMetricValue(item.id, MetricType.CONNECTION_STATUS)) ===
          DeviceStatus.NORMAL
            ? 1
            : 0;
        return item;
      });

      const newDeviceData = await Promise.all(promises);

      // 返回明确类型的响应数据
      return c.json(newDeviceData as DeviceWithAssetInfo[]);
    } catch (error) {
      console.error("获取设备实例数据失败:", error);
      return c.json([]);
    }
  });

  // 告警设备TOP数据
  bigRoutes.get("/zichan_alarm_device", async (c) => {
    try {
      const apiClient = c.get('apiClient');

      // 直接使用查询参数
      const limit = Number(c.req.query("limit")) || 5;

      // 查询告警次数最多的设备TOP10
      const alarmDevices = await apiClient.database.raw(
        `
          SELECT 
            zi.asset_name AS deviceName,
            COUNT(da.id) AS alarmCount,
            ROW_NUMBER() OVER (ORDER BY COUNT(da.id) DESC) AS \`rank\`
          FROM 
            device_alerts da
          LEFT JOIN 
            zichan_info zi ON da.device_id = zi.id
          WHERE 
            da.is_deleted = ${DeleteStatus.NOT_DELETED}
            AND zi.is_deleted = ${DeleteStatus.NOT_DELETED}
          GROUP BY 
            da.device_id, zi.asset_name
          ORDER BY 
            alarmCount DESC
          LIMIT ?
        `,
        [limit]
      );

      // 返回明确类型的响应数据
      return c.json(alarmDevices as AlarmDeviceData[]);
    } catch (error) {
      console.error("获取告警设备TOP数据失败:", error);
      return c.json([]);
    }
  });

  // 机柜数据
  bigRoutes.get("/rack", async (c) => {
    try {
      const apiClient = c.get('apiClient');
      const query = c.req.query();

      // 直接使用查询参数
      const isDeleted = query.is_deleted === "1" ? 1 : 0;

      // 查询机柜数据
      const rackData = (await apiClient.database
        .table("rack_info")
        .where("is_deleted", isDeleted)
        .select("*")) as RackInfo[];

      return c.json(rackData);
    } catch (error) {
      console.error("获取机柜数据失败:", error);
      return c.json([]);
    }
  });

  // 机柜服务器数据
  bigRoutes.get("/rack-server", async (c) => {
    try {
      const apiClient = c.get('apiClient');
      const query = c.req.query();

      // 直接使用查询参数
      const rackId = query.rack_id;
      const isDeleted = query.is_deleted === "1" ? 1 : 0;

      // 查询条件构建
      const queryBuilder = apiClient.database
        .table("rack_server as rs")
        .leftJoin("zichan_info as zi", "rs.asset_id", "zi.id")
        .leftJoin("device_instances as di", "rs.asset_id", "di.id")
        .leftJoin("rack_info as ri", "rs.rack_id", "ri.id")
        .where("rs.is_deleted", isDeleted)
        .select(
          "rs.*",
          "zi.asset_name",
          "zi.device_category",
          "zi.device_status",
          "zi.network_status",
          "zi.packet_loss",
          "zi.cpu",
          "zi.memory",
          "zi.disk",
          "ri.rack_name",
          "ri.rack_code",
          "di.address as ip_address"
        );

      // 添加机柜ID过滤条件
      if (rackId) {
        queryBuilder.where("rs.rack_id", rackId);
      }

      const rackServerData = await queryBuilder;

      return c.json(rackServerData);
    } catch (error) {
      console.error("获取机柜服务器数据失败:", error);
      return c.json([]);
    }
  });

  // 服务器类型贴图
  bigRoutes.get("/rack/server/type/image", async (c) => {
    try {
      const apiClient = c.get('apiClient');
      const query = c.req.query();

      // 直接使用查询参数
      const isEnabled = query.is_enabled === "1" ? 1 : 0;
      const isDeleted = query.is_deleted === "1" ? 1 : 0;

      // 分页参数
      const page = parseInt(query.page || "1");
      const pageSize = parseInt(query.pageSize || "100");

      // 查询条件构建
      const queryBuilder = apiClient.database
        .table("rack_server_type")
        .where("is_deleted", isDeleted)
        .select("*")
        .orderBy("id", "asc");

      // 添加过滤条件
      if (isEnabled !== undefined) {
        queryBuilder.where("is_enabled", isEnabled);
      }

      // 应用分页
      const iconData = await queryBuilder
        .limit(pageSize)
        .offset((page - 1) * pageSize);

      return c.json(iconData);
    } catch (error) {
      console.error("获取服务器类型贴图失败:", error);
      return c.json([]);
    }
  });

  return bigRoutes;
}