File Manager

✏️ Edit File: /home/u625410797/domains/monothailand.com/public_html/smart_panel/api/v1/dashboard/energy_chart.php

<?php
// =============================================================================
// API: DASHBOARD ENERGY CHART DATA
// Method: GET
// Description: Returns time-series energy data for chart rendering
// Auth: JWT Required
// Params: ?period=today|week|month|year  &site_id=<optional>  &room_id=<optional>
// =============================================================================

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

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

if ($_SERVER['REQUEST_METHOD'] !== 'GET') {
    Response::error(405, "Method Not Allowed");
}

$auth = Security::requireAuth();
$userId = $auth['user_id'] ?? $auth['id'] ?? null;

$period = isset($_GET['period']) ? Security::sanitize($_GET['period']) : 'today';
$siteId = isset($_GET['site_id']) ? Security::sanitize($_GET['site_id']) : null;
$roomId = isset($_GET['room_id']) ? Security::sanitize($_GET['room_id']) : null;

// Determine grouping and date range
switch ($period) {
    case 'today':
        $grouping = "DATE_FORMAT(t.recorded_at, '%H:00')";
        $dateFilter = "t.recorded_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
        $labelFormat = "CONCAT(DATE_FORMAT(t.recorded_at, '%H'), ':00')";
        break;
    case 'week':
        $grouping = "DATE(t.recorded_at)";
        $dateFilter = "t.recorded_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
        $labelFormat = "DATE_FORMAT(t.recorded_at, '%a')";
        break;
    case 'month':
        $grouping = "DATE(t.recorded_at)";
        $dateFilter = "t.recorded_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
        $labelFormat = "DATE_FORMAT(t.recorded_at, '%d/%m')";
        break;
    case 'year':
        $grouping = "DATE_FORMAT(t.recorded_at, '%Y-%m')";
        $dateFilter = "t.recorded_at >= DATE_SUB(NOW(), INTERVAL 365 DAY)";
        $labelFormat = "DATE_FORMAT(t.recorded_at, '%b')";
        break;
    default:
        $grouping = "DATE_FORMAT(t.recorded_at, '%H:00')";
        $dateFilter = "t.recorded_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
        $labelFormat = "DATE_FORMAT(t.recorded_at, '%H:00')";
}

try {
    // Dynamic Check: Fast-path via static schema knowledge
    $ampCol = Database::detectColumn('telemetry', 'amp_a') ? 'amp_a' : 'current_a';
    $coilCol = Database::detectColumn('telemetry', 'coil_temp_c') ? 't.coil_temp_c' : 'NULL';

// Build site/room filter for slot gathering
    $siteFilter = '';
    $slotParams = [$userId];

if ($siteId) {
        $siteFilter .= ' AND s.id = ?';
        $slotParams[] = $siteId;
    }
    if ($roomId) {
        $siteFilter .= ' AND r.id = ?';
        $slotParams[] = $roomId;
    }

// 1. Gather slot_ids with PRIORITY MAP (Unified Priority + Fallback)
    // Master and Slave measure the SAME load → never SUM, use Priority Pick
    $slotStmt = $pdo->prepare("
        SELECT ds.id, ds.mac_address, ds.mac_pzem
        FROM device_slots ds
        JOIN rooms r ON ds.room_id = r.id
        JOIN sites s ON r.site_id = s.id
        WHERE s.user_id = ? $siteFilter
    ");
    $slotStmt->execute($slotParams);
    $userSlots = $slotStmt->fetchAll(PDO::FETCH_ASSOC);

$allSlotIds = [];
    $slotPriority = []; // slot_id => priority (1=Slave PZEM, 2=Master)
    foreach ($userSlots as $slot) {
        // Master identifiers (priority 2)
        $allSlotIds[] = $slot['id'];
        $slotPriority[$slot['id']] = 2;
        if (!empty($slot['mac_address'])) {
            $cleanMac = str_replace([':', '.', '-', ' '], '', strtoupper($slot['mac_address']));
            $ecoId = 'Smart_Panel_' . $cleanMac;
            $allSlotIds[] = $ecoId;
            $slotPriority[$ecoId] = 2;
        }
        // Slave PZEM identifiers (priority 1 - highest)
        if (!empty($slot['mac_pzem'])) {
            $cleanPzem = str_replace([':', '.', '-', ' '], '', strtoupper($slot['mac_pzem']));
            if ($cleanPzem !== '000000000000') {
                $ecoIdPzem = 'Smart_Panel_' . $cleanPzem;
                $allSlotIds[] = $ecoIdPzem;
                $slotPriority[$ecoIdPzem] = 1;
            }
        }
    }
    $allSlotIds = array_values(array_unique(array_filter($allSlotIds)));

$data = [];

if (!empty($allSlotIds)) {
        $placeholders = implode(',', array_fill(0, count($allSlotIds), '?'));

// 2. Query per slot_id (no outer SUM), PHP does Priority Pick
        $sql = "
            SELECT
                time_group,
                label,
                slot_id,
                ROUND(unit_avg_power, 1) as avg_power,
                ROUND(unit_max_power, 1) as max_power,
                ROUND(unit_energy, 4) as total_energy,
                ROUND(unit_avg_temp, 1) as avg_temp,
                ROUND(unit_avg_hum, 1) as avg_humidity,
                ROUND(unit_avg_coil, 1) as avg_coil_temp,
                ROUND(unit_avg_volt, 1) as avg_voltage,
                ROUND(unit_avg_curr, 2) as avg_current,
                unit_samples as sample_count
            FROM (
                SELECT
                    $grouping as time_group,
                    $labelFormat as label,
                    t.slot_id,
                    AVG(COALESCE(t.power_w, 0)) as unit_avg_power,
                    MAX(COALESCE(t.power_w, 0)) as unit_max_power,
                    COALESCE(MAX(t.energy_kwh) - MIN(t.energy_kwh), 0) as unit_energy,
                    AVG(COALESCE(t.temp_c, 0)) as unit_avg_temp,
                    AVG(COALESCE(t.humidity_rh, 0)) as unit_avg_hum,
                    AVG(COALESCE($coilCol, 0)) as unit_avg_coil,
                    AVG(COALESCE(t.voltage_v, 0)) as unit_avg_volt,
                    AVG(COALESCE(t.$ampCol, 0)) as unit_avg_curr,
                    COUNT(*) as unit_samples
                FROM telemetry t
                WHERE t.slot_id IN ($placeholders) AND $dateFilter
                GROUP BY time_group, label, t.slot_id
            ) as unit_buckets
            ORDER BY time_group ASC
        ";

$stmt = $pdo->prepare($sql);
        $stmt->execute($allSlotIds);
        $rawRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 3. Priority Pick per time_group: use highest-priority slot_id per bucket
        $bucketData = []; // time_group => ['priority' => N, row => ...]
        foreach ($rawRows as $row) {
            $tg = $row['time_group'];
            $sid = $row['slot_id'];
            $p = $slotPriority[$sid] ?? 99;

if (!isset($bucketData[$tg]) || $p < $bucketData[$tg]['priority']) {
                $bucketData[$tg] = ['priority' => $p, 'row' => $row];
            } else if ($p == $bucketData[$tg]['priority']) {
                // Same priority (shouldn't happen often) → keep one with more samples
                if (($row['sample_count'] ?? 0) > ($bucketData[$tg]['row']['sample_count'] ?? 0)) {
                    $bucketData[$tg] = ['priority' => $p, 'row' => $row];
                }
            }
        }

// Build final data with type casting
        foreach ($bucketData as $tg => $entry) {
            $row = $entry['row'];
            unset($row['slot_id']); // Remove internal field
            $row['avg_power'] = (float)($row['avg_power'] ?? 0);
            $row['max_power'] = (float)($row['max_power'] ?? 0);
            $row['total_energy'] = (float)($row['total_energy'] ?? 0);
            $row['avg_temp'] = (float)($row['avg_temp'] ?? 0);
            $row['avg_humidity'] = (float)($row['avg_humidity'] ?? 0);
            $row['avg_coil_temp'] = (float)($row['avg_coil_temp'] ?? 0);
            $row['avg_voltage'] = (float)($row['avg_voltage'] ?? 0);
            $row['avg_current'] = (float)($row['avg_current'] ?? 0);
            $row['sample_count'] = (int)($row['sample_count'] ?? 0);
            $data[] = $row;
        }
    }

// [MASTERPIECE] Fallback: Empty list handling
    if (empty($data)) {
        $data = []; 
    }

Response::success([
        'period' => $period,
        'chart_data' => $data,
        'data_points' => count($data),
    ], "Energy chart data generated");

} catch (Throwable $e) {
    error_log("Energy Chart Error: " . $e->getMessage());
    Response::error(500, "Internal Server Error", $e->getMessage());
}
?>

Batal

⬅ Naik ke v1 6 item

Nama	Tipe	Ukuran	Diubah	Aksi
🐘detailed_comparison.php	php	20.3 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘energy_chart.php	php	8.2 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘energy_comparison.php	php	20.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘location_breakdown.php	php	10.5 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘summary.php	php	16.4 KB	2026-04-30 09:54	✏️ Edit 👁️ View
🐘test_reach.php	php	90 B	2026-04-30 09:54	✏️ Edit 👁️ View