1#@!#!123s

: / var / www / ipwdn / json_mysql /
Filename : json_import.php
back
<!-- 
<script>
// setTimeout(function () { window.location.reload(); }, 1*60*1000);
// // just show current time stamp to see time of last refresh.
// document.write(new Date());
</script> -->


<?php
// ini_set('display_errors', 1);
// ini_set('display_startup_errors', 1);

$servername = "localhost";
$username = "root";
$password = "HbM#78Kp";
$dbname = "ipwdn";

// Database connection
$conn = new mysqli($servername, $username, $password, $dbname);

// DS: pobranie aktualnych danych
$result = $conn->query('SELECT timestamp, device FROM ipwdn_meteo_data_current');
$current_data = $result->fetch_all(MYSQLI_ASSOC);

$path = "/var/www/ipwdn/json_export";

// sortowanie po dacie i pobranie najnowszego
foreach(new DirectoryIterator($path) as $item) {
    if ($item->isFile() && $item->getExtension() === 'json' && $item->getSize() > 0 && (empty($file) || $item->getMTime() > $file->getMTime())) {
        $file = clone $item;
    }
}
// echo '<br />';
// echo '<br />';
// echo $file;
// echo '<br />';

if ($file) {
    $jsoncontent = file_get_contents($file->getPathname());
    $json_decode = json_decode($jsoncontent, true);

    foreach ($json_decode['data'] as $device => $data) {
        if (isset($data['dtimestamp']) && isset($data['timestamp'])) {
            $time = $data['dtimestamp'];
        } else if (isset($data['timestamp'])) {
            $time = strtotime($data['timestamp']);
        } else {
            $time = null;
        }

        if ($time) {
            $id = $time.'_'.$device;
            // echo $id;
            $data['device'] = $device;
            $jsondata = json_encode($data);
            //Insert data Query
            $sql = "INSERT INTO ipwdn_meteo_data (id, timestamp, device, data) VALUES ('$id', FROM_UNIXTIME('$time'), '$device', '$jsondata')";
            if ($conn->query($sql) === TRUE) {
                // echo '<p style="color: blue;">Insert your JSON record successfully</p>';
            } else {
                // echo '<p style="color: red;">Insert your JSON record failed (Record exists)</p>';
            }

            // DS: update istniejących danych 
            foreach($current_data as $cdata) {
                // DS: jezeli device jest w bazie
                if ($cdata['device'] === $device) {
                    // DS: jeżeli czas jest większy od zapisanego w bazie
                    if ($time > strtotime($cdata['timestamp'])) {
                        $sql = "UPDATE ipwdn_meteo_data_current SET id = '$id', timestamp = FROM_UNIXTIME('$time'), data = '$jsondata' WHERE device = '$device'";
                        echo $sql;
                        if ($conn->query($sql) === TRUE) {
                            // echo '<p style="color: blue;">UPDATE your JSON record successfully</p>';
                        } else {
                            // echo '<p style="color: red;">UPDATE failed</p>';
                        }
                    }
                }
            }

        } else {
            // echo '<p style="color: red;">No data!!!</p>';
        }
    }
}

?>