File Manager

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

<?php
// =============================================================================
// ECOTECH HISTORY PRECISION API (MASTERPIECE V5 - INFINITE PERFORMANCE)
// SQL-Level Aggregation with NULLIF Protection
// Optimized to handle millions of records in milliseconds without 504 Timeouts.
// =============================================================================
header("Content-Type: application/json; charset=UTF-8");
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization, X-Api-Key');
require_once '../../config.php';

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

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

$slotId = $_GET['slot_id'] ?? null;
$period = $_GET['period'] ?? 'day'; 
$baseDate = $_GET['base_date'] ?? date('Y-m-d');
$fromDate = $_GET['from_date'] ?? null;

if (!$slotId) {
    Response::error(400, "Missing slot_id");
}

$pdo = Database::getConnection();

try {
    // 1. UNIFIED PRIORITY + FALLBACK RESOLUTION
    $targetMacs = [];
    $macPriority = []; // MAC => priority (1=Slave, 2=Master)
    $macToSlot = [];  // MAC => slotId (for per-slot aggregation in 'all' mode)
    $isAll = ($slotId === 'all');
    if ($isAll) {
        $siteId = $_GET['site_id'] ?? null;
        if ($siteId) {
            $stmt = $pdo->prepare("SELECT ds.id, ds.mac_address, ds.mac_pzem FROM device_slots ds JOIN rooms r ON ds.room_id = r.id WHERE r.site_id = ?");
            $stmt->execute([$siteId]);
        } else {
            $stmt = $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 = ?
            ");
            $stmt->execute([$userId]);
        }
        $rows = $stmt->fetchAll();
    } else {
        $stmt = $pdo->prepare("SELECT mac_address, mac_pzem FROM device_slots WHERE id = ? LIMIT 1");
        $stmt->execute([$slotId]);
        $rows = $stmt->fetchAll();
    }

foreach ($rows as $row) {
        $m = strtoupper(str_replace([':', '-', '.'], '', $row['mac_address']));
        $s = strtoupper(str_replace([':', '-', '.'], '', $row['mac_pzem'] ?? ''));
        $slotKey = $isAll ? $row['id'] : $slotId;
        // Collect BOTH with priority for per-day fallback
        if (!empty($s) && $s !== '000000000000') {
            $targetMacs[] = $s;
            $macPriority[$s] = 1; // PZEM Slave = highest
            $macToSlot[$s] = $slotKey;
        }
        if (!empty($m)) {
            $targetMacs[] = $m;
            $macPriority[$m] = 2; // Master = fallback
            $macToSlot[$m] = $slotKey;
        }
    }
    $targetMacs = array_values(array_unique(array_filter($targetMacs)));

if (empty($targetMacs)) {
        if ($slotId !== 'all') {
            // [FIX] Allow fallback to slot_id query even when MAC addresses
            // are not configured in device_slots.
        } else {
            Response::success([], "No units identified.");
        }
    }

// 2. Precise Date Boundaries — Period-Aware
    if ($fromDate) {
        $start = $fromDate;
    } else {
        switch ($period) {
            case 'year':
                $start = date('Y-m-d', strtotime('-365 days', strtotime($baseDate)));
                break;
            case 'lifetime':
                $start = '2020-01-01';
                break;
            case 'month':
                $start = date('Y-m-01', strtotime($baseDate));
                break;
            default:
                $start = date('Y-m-d', strtotime('-30 days', strtotime($baseDate)));
        }
    }
    $end = date('Y-m-d', strtotime($baseDate));
    
    $startTime = $start . ' 00:00:00';
    $endTime = date('Y-m-d 23:59:59', strtotime($end));

// 2.5 SHARD DISCOVERY (MASTERPIECE V6 - REGISTRY-AWARE SYNC)
    $segments = [];
    // Always include the main telemetry table as the first segment
    $segments[] = ['pdo' => $pdo, 'db_name' => DB_NAME, 'is_remote' => false];

try {
        $regStmt = $pdo->prepare("
            SELECT db_name, db_user, db_pass 
            FROM db_registry 
            WHERE start_date <= ? 
              AND (end_date >= ? OR end_date IS NULL)
        ");
        $regStmt->execute([$endTime, $startTime]);
        $regs = $regStmt->fetchAll();
        
        foreach ($regs as $r) {
            if ($r['db_name'] === DB_NAME) continue;
            $sPdo = Database::getRemoteConnection($r['db_name'], $r['db_user'], $r['db_pass']);
            if ($sPdo) {
                $segments[] = [
                    'pdo' => $sPdo,
                    'db_name' => $r['db_name'],
                    'is_remote' => true
                ];
            }
        }
    } catch (Exception $e) {
        // Fallback: If db_registry doesn't exist, proceed with Main DB only
        error_log("Precision History: Shard discovery skipped - " . $e->getMessage());
    }

// 2.7 MAC List Preparation (MASTERPIECE V6 - SQL INJECTION SAFE)
    $macListArr = [];
    foreach ($targetMacs as $m) {
        $mClean = strtoupper(preg_replace('/[^A-Fa-f0-9]/', '', $m));
        $macListArr[] = $pdo->quote($mClean);
        $withColons = implode(':', str_split($mClean, 2));
        $macListArr[] = $pdo->quote($withColons);
    }
    $macList = !empty($macListArr) ? implode(',', $macListArr) : "''";

// 3. MASTERPIECE SQL ENGINE (V6 - THE SHARD COMMANDER)
    $dailyResults = []; // date => [mac => [min_e, max_e, avg_p, min_t, max_t]]

foreach ($segments as $seg) {
        $segPdo = $seg['pdo'];
        $hasE2 = Database::detectColumn('telemetry', 'energy_kwh2', $segPdo);
        $hasE3 = Database::detectColumn('telemetry', 'energy_kwh3', $segPdo);
        $hasP2 = Database::detectColumn('telemetry', 'power_w2', $segPdo);
        $hasP3 = Database::detectColumn('telemetry', 'power_w3', $segPdo);
        $hasTotalP = Database::detectColumn('telemetry', 'total_power_w', $segPdo);
        $hasTotalE = Database::detectColumn('telemetry', 'total_energy_kwh', $segPdo);

// [FIX] Energy: prefer total_energy_kwh, else sum per-phase.
        $eExpr = $hasTotalE
            ? "COALESCE(NULLIF(total_energy_kwh, 0), COALESCE(energy_kwh, 0)" . ($hasE2 ? " + COALESCE(energy_kwh2,0)" : "") . ($hasE3 ? " + COALESCE(energy_kwh3,0)" : "") . ")"
            : "COALESCE(energy_kwh, 0)" . ($hasE2 ? " + COALESCE(energy_kwh2,0)" : "") . ($hasE3 ? " + COALESCE(energy_kwh3,0)" : "");
        // [FIX] Total Power Strategy: Prefer per-phase sum (L1+L2+L3) when columns exist,
        // because total_power_w may contain stale L1-only values from older firmware.
        if ($hasP2 || $hasP3) {
            $pExpr = "COALESCE(power_w, 0)" . ($hasP2 ? " + COALESCE(power_w2,0)" : "") . ($hasP3 ? " + COALESCE(power_w3,0)" : "");
        } else if ($hasTotalP) {
            $pExpr = "COALESCE(NULLIF(total_power_w, 0), COALESCE(power_w, 0))";
        } else {
            $pExpr = "COALESCE(power_w, 0)";
        }

$sql = "
            SELECT 
                DATE(recorded_at) as log_date, 
                mac_address,
                MIN(NULLIF($eExpr, 0)) as min_e,
                MAX($eExpr) as max_e,
                AVG($pExpr) as avg_p,
                MIN(recorded_at) as min_t,
                MAX(recorded_at) as max_t
            FROM telemetry
            WHERE (mac_address IN ($macList) OR slot_id = ?)
            AND recorded_at >= ? AND recorded_at <= ?
            GROUP BY log_date, mac_address
        ";

$stmt = $segPdo->prepare($sql);
        $stmt->execute([$slotId, $startTime, $endTime]);

while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $date = $r['log_date'];
            $mac = strtoupper(str_replace([':', '-', '.'], '', $r['mac_address']));
            
            if (!isset($dailyResults[$date][$mac])) {
                $dailyResults[$date][$mac] = [
                    'min_e' => (float)$r['min_e'],
                    'max_e' => (float)$r['max_e'],
                    'avg_p' => (float)$r['avg_p'],
                    'min_t' => $r['min_t'],
                    'max_t' => $r['max_t'],
                    'cnt'   => 1
                ];
            } else {
                // Merge data for the same day from different shards (e.g. rollover)
                $existing = &$dailyResults[$date][$mac];
                if ($r['min_e'] > 0 && ($existing['min_e'] == 0 || $r['min_e'] < $existing['min_e'])) {
                    $existing['min_e'] = (float)$r['min_e'];
                }
                if ($r['max_e'] > $existing['max_e']) {
                    $existing['max_e'] = (float)$r['max_e'];
                }
                if (strtotime($r['min_t']) < strtotime($existing['min_t'])) {
                    $existing['min_t'] = $r['min_t'];
                }
                if (strtotime($r['max_t']) > strtotime($existing['max_t'])) {
                    $existing['max_t'] = $r['max_t'];
                }
                // Weighted average for power
                $existing['avg_p'] = ($existing['avg_p'] * $existing['cnt'] + (float)$r['avg_p']) / ($existing['cnt'] + 1);
                $existing['cnt']++;
            }
        }
        if ($seg['is_remote']) $seg['pdo'] = null; // Close remote connections
    }

// 4. DATASET CONSOLIDATION (Per-Slot MAC Selection + Cross-Slot Summation)
    // For 'all' mode: select best MAC per slot per day, then SUM deltas across slots.
    // For single slot: select best MAC per day (same as before).
    $finalDeltas = []; // date => kwh
    foreach ($dailyResults as $date => $macs) {
        // Group MACs by slot for per-slot selection
        $perSlotDeltas = []; // slotKey => bestDelta
        
        foreach ($macs as $mac => $d) {
            $priority = $macPriority[$mac] ?? 99;
            $slotKey = $macToSlot[$mac] ?? '__unknown__';
            
            // Calculate delta for this device on this day
            $delta = $d['max_e'] - $d['min_e'];
            
            // Validation: Riemann Sum fallback if delta is anomalous or zero with power
            if ($delta > 100 || $delta < 0 || ($delta <= 0 && $d['avg_p'] > 10)) {
                $duration = strtotime($d['max_t']) - strtotime($d['min_t']);
                $delta = ($d['avg_p'] * $duration) / 3600 / 1000;
            }

// Per-slot best MAC selection (priority-based)
            if (!isset($perSlotDeltas[$slotKey])) {
                $perSlotDeltas[$slotKey] = ['delta' => 0, 'priority' => 999];
            }
            
            if ($delta > 0) {
                if ($perSlotDeltas[$slotKey]['delta'] == 0 || $priority < $perSlotDeltas[$slotKey]['priority']) {
                    $perSlotDeltas[$slotKey] = ['delta' => $delta, 'priority' => $priority];
                }
            } else if ($perSlotDeltas[$slotKey]['delta'] == 0 && $priority < $perSlotDeltas[$slotKey]['priority']) {
                $perSlotDeltas[$slotKey] = ['delta' => 0, 'priority' => $priority];
            }
        }
        
        // Sum best deltas across all slots (for 'all' mode, this sums all units)
        // For single slot, there's only one slotKey, so sum = that one delta
        $totalDelta = 0;
        foreach ($perSlotDeltas as $slotKey => $sd) {
            $totalDelta += $sd['delta'];
        }
        $finalDeltas[$date] = $totalDelta;
    }

// 4. DATASET NORMALIZATION (Empty day padding)
    $masterDataset = [];
    $current = strtotime($start);
    $endTs = strtotime($end);
    
    while ($current <= $endTs) {
        $dateStr = date('Y-m-d', $current);
        $masterDataset[] = [
            'label' => $dateStr,
            'kwh'   => round($finalDeltas[$dateStr] ?? 0, 3)
        ];
        $current = strtotime('+1 day', $current);
    }

Response::success($masterDataset, "Precision Engine V5: Infinite Performance Mode Active.");

} catch (Exception $e) {
    Response::error(500, "Precision Engine V5 Failed", $e->getMessage());
}

Batal

⬅ Naik ke api 106 item

Nama	Tipe	Ukuran	Diubah	Aksi
📁admin	dir	—	2026-03-20 09:16	📂 Buka
📁alerts	dir	—	2026-03-20 06:33	📂 Buka
📁auth	dir	—	2026-02-05 17:53	📂 Buka
📁dashboard	dir	—	2026-04-24 01:53	📂 Buka
📁industrial	dir	—	2026-03-16 16:14	📂 Buka
📁ota	dir	—	2026-04-02 09:10	📂 Buka
📁programs	dir	—	2026-02-08 08:58	📂 Buka
🐘active_ping.php	php	995 B	2026-03-28 05:59	✏️ Edit 👁️ View
🐘active_tracker.php	php	1.9 KB	2026-06-30 07:22	✏️ Edit 👁️ View
🐘alert_history.php	php	5 KB	2026-03-27 03:50	✏️ Edit 👁️ View
🐘analytics_engine.php	php	6 KB	2026-02-28 12:46	✏️ Edit 👁️ View
🐘audit_logs.php	php	1.9 KB	2026-02-24 15:21	✏️ Edit 👁️ View
🐘backfill_energy.php	php	1.2 KB	2026-03-27 03:50	✏️ Edit 👁️ View
🐘bind_hardware.php	php	2.7 KB	2026-03-03 09:58	✏️ Edit 👁️ View
🐘bot_slots.php	php	2.5 KB	2026-02-21 07:20	✏️ Edit 👁️ View
🐘check_serial.php	php	4.9 KB	2026-02-23 09:27	✏️ Edit 👁️ View
🐘check_tz.php	php	427 B	2026-03-14 05:28	✏️ Edit 👁️ View
🐘complete_command.php	php	3.1 KB	2026-02-23 10:26	✏️ Edit 👁️ View
🐘control.php	php	14.1 KB	2026-06-30 07:04	✏️ Edit 👁️ View
🐘control_mass.php	php	1.9 KB	2026-03-14 07:35	✏️ Edit 👁️ View
🐘db_explain.php	php	1.1 KB	2026-03-27 07:12	✏️ Edit 👁️ View
🐘db_optimize.php	php	1.3 KB	2026-03-27 07:13	✏️ Edit 👁️ View
🐘debug_cumulative.php	php	1.5 KB	2026-03-27 03:49	✏️ Edit 👁️ View
🐘debug_db.php	php	3.4 KB	2026-02-23 10:27	✏️ Edit 👁️ View
🐘debug_db_check.php	php	757 B	2026-03-17 15:21	✏️ Edit 👁️ View
🐘debug_energy.php	php	901 B	2026-03-27 03:50	✏️ Edit 👁️ View
🐘debug_history_logic.php	php	1.7 KB	2026-03-14 05:28	✏️ Edit 👁️ View
🐘debug_product69.php	php	8.9 KB	2026-07-03 07:31	✏️ Edit 👁️ View
🐘debug_raw_data.php	php	1.7 KB	2026-03-14 05:28	✏️ Edit 👁️ View
🐘debug_slot.php	php	852 B	2026-03-09 09:10	✏️ Edit 👁️ View
🐘debug_wana.php	php	973 B	2026-03-27 07:04	✏️ Edit 👁️ View
🐘delete_node_permanent.php	php	6.3 KB	2026-03-27 03:51	✏️ Edit 👁️ View
🐘delete_program.php	php	2.8 KB	2026-02-21 07:21	✏️ Edit 👁️ View
🐘delete_site.php	php	4.5 KB	2026-02-21 07:22	✏️ Edit 👁️ View
🐘delete_slot.php	php	2.5 KB	2026-02-23 09:27	✏️ Edit 👁️ View
🐘dump_debug.php	php	1.1 KB	2026-02-21 07:22	✏️ Edit 👁️ View
🐘energy_anchor.php	php	2.1 KB	2026-06-30 07:04	✏️ Edit 👁️ View
🐘energy_integration_engine.php	php	4.6 KB	2026-06-30 07:04	✏️ Edit 👁️ View
🐘firmware_core.php	php	5.5 KB	2026-02-24 15:20	✏️ Edit 👁️ View
🐘fix_schema.php	php	4.7 KB	2026-06-29 03:03	✏️ Edit 👁️ View
🐘fleet_stats.php	php	4.4 KB	2026-02-24 01:33	✏️ Edit 👁️ View
🐘geo_intelligence.php	php	4.1 KB	2026-03-01 13:44	✏️ Edit 👁️ View
🐘get_commands.php	php	4.2 KB	2026-06-30 07:22	✏️ Edit 👁️ View
🐘get_commands_backup.php	php	1.8 KB	2026-02-21 07:23	✏️ Edit 👁️ View
🐘get_hidden_nodes.php	php	1.2 KB	2026-03-17 15:28	✏️ Edit 👁️ View
🐘get_hierarchy.php	php	15.4 KB	2026-03-18 02:23	✏️ Edit 👁️ View
🐘get_slot_program.php	php	2 KB	2026-02-21 07:24	✏️ Edit 👁️ View
🐘health_analysis.php	php	6.2 KB	2026-06-30 07:04	✏️ Edit 👁️ View
🐘history.php	php	30.5 KB	2026-07-03 06:29	✏️ Edit 👁️ View
🐘history_backup1.php	php	9.8 KB	2026-04-23 06:40	✏️ Edit 👁️ View
🐘history_day_OK.php	php	18.4 KB	2026-03-23 11:49	✏️ Edit 👁️ View
🐘history_new_bug.php	php	17.8 KB	2026-03-28 02:09	✏️ Edit 👁️ View
🐘history_precision.php	php	11.9 KB	2026-06-30 07:04	✏️ Edit 👁️ View
🐘history_precision_b1.php	php	4.5 KB	2026-03-27 05:32	✏️ Edit 👁️ View
🐘history_precision_b2.php	php	5.6 KB	2026-04-23 06:39	✏️ Edit 👁️ View
🐘history_precision_day_OK.php	php	10 KB	2026-03-23 11:49	✏️ Edit 👁️ View
🐘hive_core.php	php	6.1 KB	2026-02-21 07:24	✏️ Edit 👁️ View
🐘inspect_db.php	php	1.9 KB	2026-03-18 01:49	✏️ Edit 👁️ View
🐘inspect_schema.php	php	990 B	2026-02-21 07:24	✏️ Edit 👁️ View
🐘latest.php	php	26.2 KB	2026-06-30 07:22	✏️ Edit 👁️ View
🐘list_devices.php	php	527 B	2026-02-21 07:25	✏️ Edit 👁️ View
🐘list_slots.php	php	2 KB	2026-02-21 07:25	✏️ Edit 👁️ View
🐘mass_command.php	php	5.5 KB	2026-02-21 07:25	✏️ Edit 👁️ View
🐘mesh_sync.php	php	3 KB	2026-02-21 07:25	✏️ Edit 👁️ View
🐘migrate_3phase.php	php	2.6 KB	2026-03-27 05:19	✏️ Edit 👁️ View
🐘move_hardware.php	php	5 KB	2026-03-07 07:37	✏️ Edit 👁️ View
🐘ota_manager.php	php	3.8 KB	2026-02-21 07:26	✏️ Edit 👁️ View
🐘peek_data.php	php	708 B	2026-02-21 07:26	✏️ Edit 👁️ View
🐘peek_recent.php	php	663 B	2026-03-09 10:42	✏️ Edit 👁️ View
📊product69_2026-07-02.csv	csv	3.1 MB	2026-07-03 07:32	✏️ Edit 👁️ View
📄purge_lock.timestamp	timestamp	10 B	2026-07-07 08:36	✏️ Edit 👁️ View
🐘push_structure.php	php	12.2 KB	2026-03-01 13:45	✏️ Edit 👁️ View
🐘register_hardware.php	php	3 KB	2026-02-21 07:26	✏️ Edit 👁️ View
🐘reset_energy.php	php	1.6 KB	2026-03-27 04:26	✏️ Edit 👁️ View
🐘room_status.php	php	5.4 KB	2026-02-21 07:49	✏️ Edit 👁️ View
🐘save_program.php	php	7.9 KB	2026-02-21 07:27	✏️ Edit 👁️ View
🐘security_digest.php	php	3.5 KB	2026-02-21 07:27	✏️ Edit 👁️ View
🐘seed_admin.php	php	1.7 KB	2026-02-21 07:27	✏️ Edit 👁️ View
🐘server_health.php	php	1.6 KB	2026-03-27 07:21	✏️ Edit 👁️ View
🐘set_node_visibility.php	php	2.1 KB	2026-03-17 14:45	✏️ Edit 👁️ View
🐘set_room_master.php	php	2.1 KB	2026-02-21 07:27	✏️ Edit 👁️ View
🐘sharing.php	php	5.1 KB	2026-03-18 01:49	✏️ Edit 👁️ View
🐘simulate_history.php	php	6.1 KB	2026-03-18 02:16	✏️ Edit 👁️ View
🐘slot_resolver.php	php	8.4 KB	2026-06-30 07:22	✏️ Edit 👁️ View
🐘status_ping.php	php	11.1 KB	2026-06-30 07:22	✏️ Edit 👁️ View
📋status_ping_error.log	log	0 B	2026-06-25 07:13	✏️ Edit 👁️ View
🐘sync.php	php	9.5 KB	2026-06-26 04:56	✏️ Edit 👁️ View
🐘sync_location_slots.php	php	3.5 KB	2026-03-27 03:51	✏️ Edit 👁️ View
🐘system_config.php	php	2.1 KB	2026-02-21 07:28	✏️ Edit 👁️ View
🐘telemetry.php	php	55.9 KB	2026-06-30 07:22	✏️ Edit 👁️ View
🐘telemetry_back1.php	php	40.6 KB	2026-04-20 09:48	✏️ Edit 👁️ View
🐘telemetry_batch.php	php	15.1 KB	2026-06-30 07:22	✏️ Edit 👁️ View
📋telemetry_batch_error.log	log	628.5 KB	2026-07-07 04:59	✏️ Edit 👁️ View
🐘telemetry_engine.php	php	6.8 KB	2026-06-30 07:04	✏️ Edit 👁️ View
📋telemetry_error.log	log	92.7 MB	2026-07-07 07:46	✏️ Edit 👁️ View
📄telemetry_error.log.20260625_132649.bak	bak	1.3 GB	2026-06-25 06:26	✏️ Edit 👁️ View
📋telemetry_raw.log	log	1.3 MB	2026-06-26 05:11	✏️ Edit 👁️ View
📄telemetry_raw.log.20260625_132649.bak	bak	6.6 GB	2026-06-25 06:26	✏️ Edit 👁️ View
🐘telemetry_raw.php	php	5.9 KB	2026-06-30 07:04	✏️ Edit 👁️ View
🐘telemetry_v2.php	php	13.9 KB	2026-06-30 07:04	✏️ Edit 👁️ View
🐘test_dual_core.php	php	1.9 KB	2026-03-23 11:58	✏️ Edit 👁️ View
🐘test_write.php	php	541 B	2026-04-23 06:20	✏️ Edit 👁️ View
🐘unlink_device.php	php	2.3 KB	2026-04-02 08:11	✏️ Edit 👁️ View
🐘update_sensor_config.php	php	3.5 KB	2026-03-14 07:36	✏️ Edit 👁️ View
🐘update_user.php	php	4.3 KB	2026-02-21 07:30	✏️ Edit 👁️ View
🐘user_manager.php	php	6.5 KB	2026-02-23 08:15	✏️ Edit 👁️ View