File Manager

✏️ Edit File: /home/u625410797/domains/monothailand.com/public_html/ecotect_air/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';
    $hasTotalP = Database::detectColumn('telemetry', 'total_power_w');
    $hasTotalE = Database::detectColumn('telemetry', 'total_energy_kwh');
    $hasE2 = Database::detectColumn('telemetry', 'energy_kwh2');
    $hasE3 = Database::detectColumn('telemetry', 'energy_kwh3');
    $pExpr = $hasTotalP ? "COALESCE(NULLIF(t.total_power_w, 0), t.power_w, 0)" : "COALESCE(t.power_w, 0)";
    // [FIX] Energy: prefer total_energy_kwh, else sum per-phase.
    $eExpr = $hasTotalE
        ? "COALESCE(NULLIF(t.total_energy_kwh, 0), COALESCE(t.energy_kwh, 0)" . ($hasE2 ? " + COALESCE(t.energy_kwh2,0)" : "") . ($hasE3 ? " + COALESCE(t.energy_kwh3,0)" : "") . ")"
        : "COALESCE(t.energy_kwh, 0)" . ($hasE2 ? " + COALESCE(t.energy_kwh2,0)" : "") . ($hasE3 ? " + COALESCE(t.energy_kwh3,0)" : "");

// 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 = 'EcoTech_' . $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 = 'EcoTech_' . $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
        // [FIX] Use COALESCE(total_power_w, power_w) to show total power for 3-phase devices
        $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($pExpr) as unit_avg_power,
                    MAX($pExpr) as unit_max_power,
                    COALESCE(MAX($eExpr) - MIN($eExpr), 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. [FIX] Value-Aware Priority Pick per time_group:
        // Prefer slot with power > 0 (real data); fall back to priority only when both are 0
        $bucketData = []; // time_group => ['priority' => N, 'has_power' => bool, row => ...]
        foreach ($rawRows as $row) {
            $tg = $row['time_group'];
            $sid = $row['slot_id'];
            $p = $slotPriority[$sid] ?? 99;
            $hasPower = (float)($row['avg_power'] ?? 0) > 0;

if (!isset($bucketData[$tg])) {
                $bucketData[$tg] = ['priority' => $p, 'has_power' => $hasPower, 'row' => $row];
            } else {
                $existing = $bucketData[$tg];
                // Prefer slot with power > 0 over one without
                if ($hasPower && !$existing['has_power']) {
                    $bucketData[$tg] = ['priority' => $p, 'has_power' => $hasPower, 'row' => $row];
                } else if (!$hasPower && $existing['has_power']) {
                    // Keep existing (it has power)
                } else if ($p < $existing['priority']) {
                    // Same power status → use priority
                    $bucketData[$tg] = ['priority' => $p, 'has_power' => $hasPower, 'row' => $row];
                } else if ($p == $existing['priority'] && $hasPower == $existing['has_power']) {
                    // Same priority → keep one with more samples
                    if (($row['sample_count'] ?? 0) > ($existing['row']['sample_count'] ?? 0)) {
                        $bucketData[$tg] = ['priority' => $p, 'has_power' => $hasPower, '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 (Exception $e) {
    error_log("Energy Chart Error: " . $e->getMessage());
    Response::error(500, "Database Error", $e->getMessage());
}
?>

Batal

⬅ Naik ke v1 13 item

Nama	Tipe	Ukuran	Diubah	Aksi
🐘detailed_comparison.php	php	20.1 KB	2026-06-27 12:58	✏️ Edit 👁️ View
🐘detailed_comparison_b1.php	php	10.1 KB	2026-04-23 15:22	✏️ Edit 👁️ View
🐘detailed_comparison_b2.php	php	12.8 KB	2026-04-23 15:56	✏️ Edit 👁️ View
🐘energy_chart.php	php	9.6 KB	2026-06-27 12:58	✏️ Edit 👁️ View
🐘energy_chart_b1.php	php	6.8 KB	2026-04-23 06:44	✏️ Edit 👁️ View
🐘energy_comparison.php	php	20.4 KB	2026-06-27 12:58	✏️ Edit 👁️ View
🐘energy_comparison_b1.php	php	14.3 KB	2026-04-23 11:31	✏️ Edit 👁️ View
🐘energy_comparison_b2.php	php	14.7 KB	2026-04-23 14:57	✏️ Edit 👁️ View
🐘energy_comparison_b3.php	php	16.6 KB	2026-04-23 17:10	✏️ Edit 👁️ View
🐘energy_comparison_bb.php	php	14 KB	2026-04-23 11:16	✏️ Edit 👁️ View
🐘location_breakdown.php	php	11.6 KB	2026-06-27 12:58	✏️ Edit 👁️ View
🐘summary.php	php	15.4 KB	2026-06-27 12:58	✏️ Edit 👁️ View
🐘summary_b1.php	php	13.9 KB	2026-04-23 06:44	✏️ Edit 👁️ View