File Manager

✏️ Edit File: /home/u625410797/domains/monothailand.com/public_html/ecotect_air/api/v1/admin/analytics.php

<?php
/**
 * Analytics API - Advanced reporting and insights
 */

header("Content-Type: application/json; charset=UTF-8");
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization, X-Api-Key');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}

require_once __DIR__ . '/../../../config.php';

$auth = Security::requireAuth();
if (!$auth['isAdmin']) {
    Response::error(403, "Forbidden", "Admin access required");
}

$method = $_SERVER['REQUEST_METHOD'];
$path = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH);
$segments = explode('/', trim($path, '/'));

// [GOD MODE] Dynamic Segment Detection
$scriptName = basename(__FILE__);
$scriptIndex = -1;
foreach ($segments as $i => $segment) {
    if ($segment === $scriptName) {
        $scriptIndex = $i;
        break;
    }
}

$reportType = ($scriptIndex !== -1 && isset($segments[$scriptIndex + 1])) ? $segments[$scriptIndex + 1] : null;

try {
    $pdo = Database::getConnection();
    
    if ($reportType) {
        switch ($reportType) {
            case 'summary':
                Response::success(getSummary($pdo, $_GET));
                break;
                
            case 'energy':
                Response::success(getEnergyReport($pdo, $_GET));
                break;
                
            case 'alerts':
                Response::success(getAlertReport($pdo, $_GET));
                break;
                
            case 'performance':
                Response::success(getPerformanceReport($pdo, $_GET));
                break;
                
            case 'comparison':
                Response::success(getComparisonReport($pdo, $_GET));
                break;
                
            default:
                Response::error(404, "Report type not found");
        }
    } else {
        http_response_code(400);
        echo json_encode(['error' => 'Report type required']);
    }
    
} catch (Exception $e) {
    Response::error(500, "Internal Server Error", "An unexpected error occurred while processing your request.", $e->getMessage());
}

// ======================== Helper Functions ========================

function getSummary($pdo, $params) {
    $period = $params['period'] ?? 'month';
    $siteId = $params['location_id'] ?? null; // Front-end still sends location_id, meaning site_id
    
    // Date range
    $dateRange = getDateRange($period);
    
    // Total device_slots
    $sql = "SELECT COUNT(*) FROM device_slots ds";
    $sqlParams = [];
    if ($siteId) {
        $sql .= " JOIN rooms r ON ds.room_id = r.id WHERE r.site_id = ?";
        $sqlParams[] = $siteId;
    }
    $stmt = $pdo->prepare($sql);
    $stmt->execute($sqlParams);
    $totalDevices = $stmt->fetchColumn();
    
    // Online device_slots
    $sql = "SELECT COUNT(*) FROM device_slots ds WHERE ds.is_online = 1";
    if ($siteId) {
        $sql .= " AND ds.room_id IN (SELECT id FROM rooms WHERE site_id = ?)";
    }
    $stmt = $pdo->prepare($sql);
    $stmt->execute($sqlParams);
    $onlineDevices = $stmt->fetchColumn();
    
    // Total energy (kWh)
    // [V13] `telemetry` holds historical snapshots. We sum power_w over intervals, or use energy_kwh if available.
    // For now, we sum `power_w` as a rough proxy if `energy_kwh` doesn't exist, but telemetry should have it.
    // Assuming `telemetry` has `power_w`. (Energy calculation usually requires timeseries integration, simplified here)
    $stmt = $pdo->prepare("SHOW COLUMNS FROM telemetry LIKE 'energy_kwh'");
    $stmt->execute();
    $hasEnergyCol = $stmt->fetch();

$energySumClause = $hasEnergyCol ? "SUM(t.energy_kwh)" : "(SUM(t.power_w) / 1000 / 60)"; // rough proxy if polling is 1/min

$sql = "
        SELECT COALESCE($energySumClause, 0) as total_energy
        FROM telemetry t
        JOIN device_slots ds ON t.slot_id = ds.id
    ";
    
    $whereClause = " WHERE t.recorded_at BETWEEN ? AND ?";
    $sqlParamsEnergy = [$dateRange['start'], $dateRange['end']];

if ($siteId) {
        $sql .= " JOIN rooms r ON ds.room_id = r.id";
        $whereClause .= " AND r.site_id = ?";
        $sqlParamsEnergy[] = $siteId;
    }
    $sql .= $whereClause;

$stmt = $pdo->prepare($sql);
    $stmt->execute($sqlParamsEnergy);
    $totalEnergy = $stmt->fetchColumn();
    
    // Total alerts
    $sql = "
        SELECT COUNT(*) as total_alerts
        FROM alerts a
        JOIN device_slots ds ON a.slot_id = ds.id
    ";
    
    $whereClause = " WHERE a.created_at BETWEEN ? AND ?";
    $sqlParamsAlert = [$dateRange['start'], $dateRange['end']];

if ($siteId) {
        $sql .= " JOIN rooms r ON ds.room_id = r.id";
        $whereClause .= " AND r.site_id = ?";
        $sqlParamsAlert[] = $siteId;
    }
    $sql .= $whereClause;

$stmt = $pdo->prepare($sql);
    $stmt->execute($sqlParamsAlert);
    $totalAlerts = $stmt->fetchColumn();
    
    // Energy cost (THB)
    $costPerKwh = 4.50; // Constant or from settings
    $totalCost = $totalEnergy * $costPerKwh;
    
    // Average power
    $sql = "
        SELECT AVG(t.power_w) as avg_power
        FROM telemetry t
        JOIN device_slots ds ON t.slot_id = ds.id
    ";

$whereClause = " WHERE t.recorded_at BETWEEN ? AND ? AND t.power_w > 0";
    $sqlParamsAvg = [$dateRange['start'], $dateRange['end']];

if ($siteId) {
        $sql .= " JOIN rooms r ON ds.room_id = r.id";
        $whereClause .= " AND r.site_id = ?";
        $sqlParamsAvg[] = $siteId;
    }
    $sql .= $whereClause;

$stmt = $pdo->prepare($sql);
    $stmt->execute($sqlParamsAvg);
    $avgPower = $stmt->fetchColumn() ?? 0;
    
    return [
        'success' => true,
        'period' => $period,
        'date_range' => $dateRange,
        'summary' => [
            'total_devices' => (int)$totalDevices,
            'online_devices' => (int)$onlineDevices,
            'offline_devices' => (int)($totalDevices - $onlineDevices),
            'total_energy_kwh' => round($totalEnergy, 2),
            'total_cost_thb' => round($totalCost, 2),
            'avg_power_w' => round($avgPower, 2),
            'total_alerts' => (int)$totalAlerts
        ]
    ];
}

function getEnergyReport($pdo, $params) {
    $period = $params['period'] ?? 'day';
    $siteId = $params['location_id'] ?? null;
    $groupBy = $params['group_by'] ?? 'hour';
    
    $dateRange = getDateRange($period);
    
    // Time series data
    $groupByFormat = match($groupBy) {
        'hour' => '%Y-%m-%d %H:00:00',
        'day' => '%Y-%m-%d',
        'week' => '%Y-%U',
        'month' => '%Y-%m',
        default => '%Y-%m-%d %H:00:00'
    };
    
    $hasE2 = Database::detectColumn('telemetry', 'energy_kwh2');
    $hasE3 = Database::detectColumn('telemetry', 'energy_kwh3');
    $hasP2 = Database::detectColumn('telemetry', 'power_w2');
    $hasP3 = Database::detectColumn('telemetry', 'power_w3');

$eExpr = "COALESCE(t.energy_kwh, 0)" . ($hasE2 ? " + COALESCE(t.energy_kwh2,0)" : "") . ($hasE3 ? " + COALESCE(t.energy_kwh3,0)" : "");
    $pExpr = "COALESCE(t.power_w, 0)" . ($hasP2 ? " + COALESCE(t.power_w2,0)" : "") . ($hasP3 ? " + COALESCE(t.power_w3,0)" : "");

// [MASTERPIECE V7] Multi-Source Energy Engine
    // If energy_kwh exists, we use it as a delta (assumes ingestion converts cumulative to delta)
    // IF the hardware energy is 0/missing but power is high, we integrate.
    $energySumClause = "SUM(CASE WHEN ($eExpr) > 0 THEN ($eExpr) ELSE (($pExpr) / 1000 / 60) END)";

$sql = "
        SELECT 
            DATE_FORMAT(t.recorded_at, ?) as time_bucket,
            COALESCE($energySumClause, 0) as total_energy,
            AVG($pExpr) as avg_power,
            MAX($pExpr) as peak_power,
            COUNT(DISTINCT t.slot_id) as device_count
        FROM telemetry t
        JOIN device_slots ds ON ds.id = (
            SELECT id FROM device_slots 
            WHERE id = t.slot_id 
               OR mac_address = REPLACE(REPLACE(REPLACE(t.mac_address, ':', ''), '-', ''), '.', '')
               OR mac_pzem = REPLACE(REPLACE(REPLACE(t.mac_address, ':', ''), '-', ''), '.', '')
            LIMIT 1
        )
    ";
    
    $sqlParams = [$groupByFormat, $dateRange['start'], $dateRange['end']];
    $whereClause = " WHERE t.recorded_at BETWEEN ? AND ?";

if ($siteId) {
        $sql .= " JOIN rooms r ON ds.room_id = r.id";
        $whereClause .= " AND r.site_id = ?";
        $sqlParams[] = $siteId;
    }
    
    $sql .= $whereClause . " GROUP BY time_bucket ORDER BY time_bucket";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($sqlParams);
    $timeSeries = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Top consumers
    $sql = "
        SELECT 
            ds.id,
            ds.mac_address,
            s.name as location_name,
            COALESCE($energySumClause, 0) as total_energy,
            AVG(t.power_w) as avg_power
        FROM device_slots ds
        LEFT JOIN telemetry t ON t.slot_id = ds.id AND t.recorded_at BETWEEN ? AND ?
        LEFT JOIN rooms r ON r.id = ds.room_id
        LEFT JOIN sites s ON s.id = r.site_id
        GROUP BY ds.id
        ORDER BY total_energy DESC
        LIMIT 10
    ";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$dateRange['start'], $dateRange['end']]);
    $topConsumers = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return [
        'success' => true,
        'period' => $period,
        'group_by' => $groupBy,
        'time_series' => $timeSeries,
        'top_consumers' => $topConsumers
    ];
}

function getAlertReport($pdo, $params) {
    $period = $params['period'] ?? 'month';
    $dateRange = getDateRange($period);
    
    // Alert statistics by severity
    $stmt = $pdo->prepare("
        SELECT 
            severity,
            COUNT(*) as count
        FROM alerts
        WHERE created_at BETWEEN ? AND ?
        GROUP BY severity
    ");
    $stmt->execute([$dateRange['start'], $dateRange['end']]);
    $bySeverity = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
    
    // Alert statistics by type
    $stmt = $pdo->prepare("
        SELECT 
            type as alert_type,
            COUNT(*) as count
        FROM alerts
        WHERE created_at BETWEEN ? AND ?
        GROUP BY type
        ORDER BY count DESC
    ");
    $stmt->execute([$dateRange['start'], $dateRange['end']]);
    $byType = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Devices with most alerts
    $stmt = $pdo->prepare("
        SELECT 
            ds.id,
            ds.mac_address,
            s.name as location_name,
            COUNT(a.id) as alert_count
        FROM device_slots ds
        LEFT JOIN alerts a ON a.slot_id = ds.id AND a.created_at BETWEEN ? AND ?
        LEFT JOIN rooms r ON r.id = ds.room_id
        LEFT JOIN sites s ON s.id = r.site_id
        GROUP BY ds.id
        HAVING alert_count > 0
        ORDER BY alert_count DESC
        LIMIT 10
    ");
    $stmt->execute([$dateRange['start'], $dateRange['end']]);
    $topAlertDevices = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return [
        'success' => true,
        'period' => $period,
        'by_severity' => $bySeverity,
        'by_type' => $byType,
        'top_alert_devices' => $topAlertDevices
    ];
}

function getPerformanceReport($pdo, $params) {
    $period = $params['period'] ?? 'month';
    $dateRange = getDateRange($period);
    
    // Average uptime / perf metrics
    $stmt = $pdo->prepare("
        SELECT 
            ds.id,
            ds.mac_address,
            s.name as location_name,
            -- Rough uptime estimate based on telemetry frequency vs expected
            COUNT(CASE WHEN t.power_w > 0 THEN 1 END) * 100.0 / NULLIF(COUNT(t.id), 0) as uptime_percent,
            AVG(t.temp_c) as avg_temp,
            AVG(t.power_w) as avg_power
        FROM device_slots ds
        LEFT JOIN telemetry t ON t.slot_id = ds.id AND t.recorded_at BETWEEN ? AND ?
        LEFT JOIN rooms r ON r.id = ds.room_id
        LEFT JOIN sites s ON s.id = r.site_id
        GROUP BY ds.id
        HAVING COUNT(t.id) > 0
        ORDER BY uptime_percent DESC
    ");
    $stmt->execute([$dateRange['start'], $dateRange['end']]);
    $devicePerformance = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    return [
        'success' => true,
        'period' => $period,
        'device_performance' => $devicePerformance
    ];
}

function getComparisonReport($pdo, $params) {
    $period = $params['period'] ?? 'month';
    $compareBy = $params['compare_by'] ?? 'location';
    $dateRange = getDateRange($period);
    
    if ($compareBy === 'location') {
        $stmt = $pdo->prepare("SHOW COLUMNS FROM telemetry LIKE 'energy_kwh'");
        $stmt->execute();
        $hasEnergyCol = $stmt->fetch();
        $energySumClause = $hasEnergyCol ? "SUM(t.energy_kwh)" : "(SUM(t.power_w) / 1000 / 60)";

$stmt = $pdo->prepare("
            SELECT 
                s.id as location_id,
                s.name as location_name,
                s.type as location_type,
                COUNT(DISTINCT ds.id) as device_count,
                COALESCE($energySumClause, 0) as total_energy,
                AVG(t.power_w) as avg_power
            FROM sites s
            LEFT JOIN rooms r ON r.site_id = s.id
            LEFT JOIN device_slots ds ON ds.room_id = r.id
            LEFT JOIN telemetry t ON t.slot_id = ds.id AND t.recorded_at BETWEEN ? AND ?
            GROUP BY s.id
            ORDER BY total_energy DESC
        ");
        $stmt->execute([$dateRange['start'], $dateRange['end']]);
        $comparison = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $comparison = [];
    }
    
    return [
        'success' => true,
        'period' => $period,
        'compare_by' => $compareBy,
        'comparison' => $comparison
    ];
}

function getDateRange($period) {
    $end = date('Y-m-d 23:59:59');
    
    $start = match($period) {
        'day' => date('Y-m-d 00:00:00'),
        'week' => date('Y-m-d 00:00:00', strtotime('-7 days')),
        'month' => date('Y-m-d 00:00:00', strtotime('-30 days')),
        'year' => date('Y-m-d 00:00:00', strtotime('-365 days')),
        default => date('Y-m-d 00:00:00', strtotime('-30 days'))
    };
    
    return [
        'start' => $start,
        'end' => $end
    ];
}

Batal

⬅ Naik ke v1 8 item

Nama	Tipe	Ukuran	Diubah	Aksi
🐘analytics.php	php	14.4 KB	2026-04-27 03:38	✏️ Edit 👁️ View
🐘firmware.php	php	9.1 KB	2026-03-20 09:16	✏️ Edit 👁️ View
🐘fleet_explorer.php	php	3.4 KB	2026-03-20 09:49	✏️ Edit 👁️ View
🐘geo_stats.php	php	1.5 KB	2026-03-20 09:20	✏️ Edit 👁️ View
📄groups.php.bak	bak	7.6 KB	2026-03-20 09:16	✏️ Edit 👁️ View
📄locations.php.bak	bak	9.7 KB	2026-03-20 09:16	✏️ Edit 👁️ View
🐘site_diagnostic.php	php	4.1 KB	2026-03-20 09:49	✏️ Edit 👁️ View
🐘users.php	php	12.1 KB	2026-03-20 09:16	✏️ Edit 👁️ View