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

import { Hono } from "hono";
import debug from "debug";
import type {
  DeviceInstance,
  DeviceType,
  DeviceTreeNode,
  MapViewDevice,
  DeviceMapStats,
  DeviceMapDataResponse,
  DeviceTreeStats
} from "../client/share/monitorTypes.ts";
import {
  DeviceStatus,
  DeviceTreeNodeType,
  DeviceTreeNodeStatus
} from "../client/share/monitorTypes.ts";
import {
  EnableStatus,
  DeleteStatus,
} from "../client/share/types.ts";

import type { Variables, WithAuth } from "./middlewares.ts";

const log = {
  api: debug("api:monitor"),
};

// 定义扩展的设备实例接口，包含从数据库查询返回的额外字段
interface DeviceInstanceExtended extends DeviceInstance {
  type_code: string;
  name?: string;
  device_status?: DeviceStatus;
  description?: string;
  longitude?: number;
  latitude?: number;
}

// 定义设备统计数据接口
interface DeviceStatisticsDB {
  type_code: string;
  total: number;
  online: number;
  offline: number;
  error: number;
}

// 定义本地统计对象接口
interface DeviceStats {
  total: number;
  online: number;
  offline: number;
  error: number;
}

// 监控数据API路由
export function createMonitorMapRoutes(withAuth: WithAuth) {
    const monitorRoutes = new Hono<{ Variables: Variables }>();
  
    // 获取设备地图监控数据列表
    monitorRoutes.get("/", withAuth, async (c) => {
      try {
        const apiClient = c.get('apiClient');
  
        // 获取分页参数
        const page = Number(c.req.query("page")) || 1;
        const limit = Number(c.req.query("limit")) || 10;
        const offset = (page - 1) * limit;
  
        // 获取筛选参数
        const deviceId = c.req.query("device_id") ? Number(c.req.query("device_id")) : undefined;
        const deviceName = c.req.query("device_name");
        const protocol = c.req.query("protocol");
        const address = c.req.query("address");
        const metricType = c.req.query("metric_type");
        const status = c.req.query("status") ? Number(c.req.query("status")) : undefined;
  
        // 构建查询
        let query = apiClient.database
          .table("device_monitor_data")
          .orderBy("collect_time", "desc");
  
        // 应用筛选条件
        if (deviceId) {
          query = query.where("device_id", deviceId);
        }
  
        if (deviceName) {
          query = query.where("device_name", "like", `%${deviceName}%`);
        }
  
        if (protocol) {
          query = query.where("protocol", protocol);
        }
  
        if (address) {
          query = query.where("address", "like", `%${address}%`);
        }
  
        if (metricType) {
          query = query.where("metric_type", metricType);
        }
  
        if (status !== undefined) {
          query = query.where("status", status);
        }
  
        // 克隆查询以获取总数
        const countQuery = query.clone();
  
        // 执行分页查询
        const monitorData = await query.limit(limit).offset(offset);
  
        // 获取总数
        const count = await countQuery.count();
  
        return c.json({
          data: monitorData,
          total: Number(count),
          page,
          pageSize: limit,
        });
      } catch (error) {
        log.api("获取设备地图监控数据失败:", error);
        return c.json({ error: "获取设备地图监控数据失败" }, 500);
      }
    });
  
    // 获取设备树数据
    monitorRoutes.get("/devices/tree", withAuth, async (c) => {
      try {
        const apiClient = c.get('apiClient');
        
        // 获取查询参数
        const status = c.req.query('status');
        const keyword = c.req.query('keyword');
        
        // 获取所有设备分类
        const deviceTypes = await apiClient.database
          .table("device_types")
          .where("is_deleted", DeleteStatus.NOT_DELETED)
          .where("is_enabled", EnableStatus.ENABLED)
          .orderBy("id", "asc") as DeviceType[];
        
        // 构建设备查询
        let deviceQuery = apiClient.database
          .raw(`
            SELECT d.*, t.code as type_code, a.asset_name as name, a.device_status as device_status
            FROM device_instances d
            LEFT JOIN device_types t ON d.type_id = t.id
            LEFT JOIN zichan_info a ON d.id = a.id
            WHERE d.is_deleted = ? AND a.is_deleted = ?
          `, [DeleteStatus.NOT_DELETED, DeleteStatus.NOT_DELETED]);
  
        // 根据状态过滤
        if (status && status !== 'all') {
          deviceQuery = apiClient.database
            .raw(`
              SELECT d.*, t.code as type_code, a.asset_name as name, a.device_status as device_status
              FROM device_instances d
              LEFT JOIN device_types t ON d.type_id = t.id
              LEFT JOIN zichan_info a ON d.id = a.id
              WHERE d.is_deleted = ? AND a.is_deleted = ?
              AND a.device_status = ?
            `, [DeleteStatus.NOT_DELETED, DeleteStatus.NOT_DELETED, 
                status === 'error' ? DeviceStatus.FAULT : 
                status === 'normal' ? DeviceStatus.NORMAL : 
                status === 'offline' ? DeviceStatus.OFFLINE : null]);
        }
        
        // 查询设备
        let devices = await deviceQuery as DeviceInstanceExtended[];
        
        // 根据关键词过滤
        if (keyword) {
          const lowercaseKeyword = keyword.toLowerCase();
          devices = devices.filter(device => 
            device.name?.toLowerCase().includes(lowercaseKeyword) ||
            device.id.toString().includes(lowercaseKeyword));
        }
        
        // 构建树结构
        const filteredTypes = deviceTypes.filter(type => {
          // 筛选当前分类下的设备
          const typeDevices = devices.filter(device => device.type_code === type.code);
          // 如果没有匹配的设备且有关键词或状态过滤，则跳过此分类
          return !(typeDevices.length === 0 && (keyword || (status && status !== 'all')));
        });
        
        const treeData: DeviceTreeNode[] = filteredTypes.map(type => {
          // 筛选当前分类下的设备
          const typeDevices = devices.filter(device => device.type_code === type.code);
          
          // 设备节点
          const deviceNodes: DeviceTreeNode[] = typeDevices.map(device => ({
            key: `device-${device.id}`,
            title: device.name || `设备${device.id}`,
            type: DeviceTreeNodeType.DEVICE,
            status: device.device_status === DeviceStatus.NORMAL ? DeviceTreeNodeStatus.NORMAL : 
                   device.device_status === DeviceStatus.FAULT ? DeviceTreeNodeStatus.ERROR : 
                   device.device_status === DeviceStatus.OFFLINE ? DeviceTreeNodeStatus.OFFLINE : DeviceTreeNodeStatus.WARNING,
            icon: type.image_url || null,
            isLeaf: true,
          }));
          
          return {
            key: type.code,
            title: type.name,
            type: DeviceTreeNodeType.CATEGORY,
            icon: type.image_url || null,
            children: deviceNodes
          };
        });
        
        return c.json({
          data: treeData
        });
      } catch (error) {
        log.api("获取设备树数据失败:", error);
        return c.json({ error: "获取设备树数据失败" }, 500);
      }
    });
    
    // 获取设备统计数据
    monitorRoutes.get("/devices/tree/statistics", withAuth, async (c) => {
      try {
        const apiClient = c.get('apiClient');
        
        // 获取所有设备分类
        const deviceTypes = await apiClient.database
          .table("device_types")
          .where("is_deleted", DeleteStatus.NOT_DELETED)
          .where("is_enabled", EnableStatus.ENABLED);
        
        // 获取设备统计
        const devicesStats = await apiClient.database
          .raw(`
            SELECT 
              t.code as type_code, 
              COUNT(*) as total,
              SUM(CASE WHEN a.device_status = ? THEN 1 ELSE 0 END) as online,
              SUM(CASE WHEN a.device_status = ? THEN 1 ELSE 0 END) as offline,
              SUM(CASE WHEN a.device_status = ? THEN 1 ELSE 0 END) as error
            FROM device_instances d
            LEFT JOIN device_types t ON d.type_id = t.id
            LEFT JOIN zichan_info a ON d.id = a.id
            WHERE d.is_deleted = ? AND a.is_deleted = ?
            GROUP BY t.code
          `, [DeviceStatus.NORMAL, DeviceStatus.OFFLINE, DeviceStatus.FAULT, DeleteStatus.NOT_DELETED, DeleteStatus.NOT_DELETED]) as DeviceStatisticsDB[];
        
        // 构建统计数据
        const statistics: DeviceTreeStats = {};
        
        deviceTypes.forEach(type => {
          const stats = devicesStats.find((stat: DeviceStatisticsDB) => 
            stat.type_code === type.code
          );
          statistics[type.code] = {
            total: stats ? Number(stats.total) : 0,
            online: stats ? Number(stats.online) : 0,
            offline: stats ? Number(stats.offline) : 0,
            error: stats ? Number(stats.error) : 0
          };
        });
        
        return c.json({
          data: statistics
        });
      } catch (error) {
        log.api("获取设备统计数据失败:", error);
        return c.json({ error: "获取设备统计数据失败" }, 500);
      }
    });
  
    // 获取设备地图数据
    monitorRoutes.get("/devices/map", withAuth, async (c) => {
      try {
        const apiClient = c.get('apiClient');
        
        // 获取筛选参数
        const typeCode = c.req.query("type_code");
        const status = c.req.query("device_status");
        const keyword = c.req.query("keyword");
        const deviceId = c.req.query("device_id");
        
        // 构建查询
        const query = apiClient.database
          .raw(`
            SELECT 
              d.id, t.code as type_code, d.remark as description,
              a.asset_name as name, a.device_status as device_status,
              a.longitude, a.latitude
            FROM device_instances d
            LEFT JOIN device_types t ON d.type_id = t.id
            LEFT JOIN zichan_info a ON d.id = a.id
            WHERE d.is_deleted = ? AND a.is_deleted = ?
            AND a.longitude IS NOT NULL AND a.latitude IS NOT NULL
          `, [DeleteStatus.NOT_DELETED, DeleteStatus.NOT_DELETED]);
        
        // 将原始数据转换为设备地图数据
        let devices = await query as DeviceInstanceExtended[];
        
        // 获取设备类型图标
        const deviceTypes = await apiClient.database
          .table("device_types")
          .where("is_deleted", DeleteStatus.NOT_DELETED);
        
        // 构建图标映射
        const iconMap: Record<string, string> = {};
        deviceTypes.forEach(type => {
          iconMap[type.code] = type.image_url || '';
        });
        
        // 应用筛选
        if (typeCode) {
          devices = devices.filter(d => d.type_code === typeCode);
        }
        
        if (status) {
          const statusNum = Number(status);
          devices = devices.filter(d => d.device_status === statusNum);
        }
        
        // 设备ID精确匹配
        if (deviceId) {
          const deviceIdNum = Number(deviceId);
          devices = devices.filter(d => d.id === deviceIdNum);
        }
        
        if (keyword) {
          const keywordLower = keyword.toLowerCase();
          devices = devices.filter(d => 
            (d.name && d.name.toLowerCase().includes(keywordLower)) || 
            (d.description && d.description.toLowerCase().includes(keywordLower))
          );
        }
        
        // 将设备转换为地图标记
        const mapDevices = devices.map(device => ({
          ...device,
          longitude: Number(device.longitude || 0),
          latitude: Number(device.latitude || 0),
          isOnline: device.device_status === DeviceStatus.NORMAL ? '1' : '0',
          image_url: iconMap[device.type_code] || '',
        })) as MapViewDevice[];
        
        // 生成统计数据
        const stats: DeviceMapStats = {
          total: devices.length,
          online: devices.filter(d => d.device_status === DeviceStatus.NORMAL).length,
          offline: devices.filter(d => d.device_status === DeviceStatus.OFFLINE).length,
          error: devices.filter(d => d.device_status === DeviceStatus.FAULT).length,
        };
        
        return c.json({
          data: mapDevices,
          stats: stats
        } as DeviceMapDataResponse);
      } catch (error) {
        log.api("获取设备地图数据失败:", error);
        return c.json({ error: "获取设备地图数据失败" }, 500);
      }
    });
  
    return monitorRoutes;
  }