Revize 41dd5f26

refs #6943: Cron - uprava pro novou verzi databaze

cron/cron.php

@@ -51 +51 @@
         }
         
         // Pridat zaznamy z vybraneho dne.
+        $insertDate = array();
         $insertRTT = array();
         $insertRT = array();
         $insertOneDay = array();
-        process_traffic_matrix($parser, $traffic, $trafficOneDay, $DAO->findFirstId("zaznam_cas"), $DAO->findFirstId("zaznam"), $DAO->findFirstId("zaznam_prum_den"), $insertRTT, $insertRT, $insertOneDay, $date->format("Y-m-d"));
-        $DAO->insertTrafficData($insertRTT, $insertRT, $insertOneDay);
+        process_traffic_matrix($parser, $traffic, $trafficOneDay, $DAO->findFirstId("datum"), $DAO->findFirstId("zaznam_cas"), $DAO->findFirstId("zaznam"), $DAO->findFirstId("zaznam_prum_den"), $insertDate, $insertRTT, $insertRT, $insertOneDay, $date->format("Y-m-d"));
+        $DAO->insertTrafficData($insertDate, $insertRTT, $insertRT, $insertOneDay);
     } else if ($date != NULL) {
         // Data pro vybrany den uz v databazi jsou.
         $logs->log(Logging::WARNING, "Pro vybrany den (".$date->format("d.m.Y").") jiz data v databazi jsou.");

cron/dao/dao.php

@@ -12 +12 @@
     }
     
     public function insertVehicles() {
-        $query = "SELECT COUNT(*) AS total FROM vozidla";
+        $query = "SELECT COUNT(*) AS total FROM vozidlo";
         $stmt = $this->dbh->executeQuery($query);
         
         if ($stmt->fetchAssoc()["total"] < 1) {
-            // Pokud v tabulce s vozidlama neni zadny zaznam, pridat vsechny moznosti.
-            $query = "INSERT INTO vozidla (id, nazev) VALUES ";
+            // Pokud v tabulce s vozidly neni zadny zaznam, pridat vsechny moznosti.
+            $query = "INSERT INTO vozidlo (id, nazev) VALUES ";
             $query .= "('0', 'Neznámé vozidlo'), ";
             $query .= "('1', 'Motocykl'), ";
             $query .= "('2', 'Auto'), ";
@@ -36 +36 @@
     public function controlTrafficData($dateStr) {
         $dateTo = new DateTime($dateStr);
         $dateTo->modify("+1 day");
-        $query = "SELECT COUNT(*) AS total FROM zaznam_cas WHERE datetime_od >= '$dateStr' AND datetime_od < '".$dateTo->format('Y-m-d')."';";
+        $query = "SELECT COUNT(*) AS total FROM datum WHERE od >= '$dateStr' AND od < '".$dateTo->format('Y-m-d')."';";
         $stmt = $this->dbh->executeQuery($query);
         return $stmt->fetchAssoc()["total"] < 1;
     }
     
-    public function insertTrafficData($insertRTT, $insertRT, $insertOneDay) {
-        for ($i = 0; $i < 3; $i++) {
+    public function insertTrafficData($insertDate, $insertRTT, $insertRT, $insertOneDay) {
+        for ($i = 0; $i < 4; $i++) {
             $query = "";
             $values = "";
             $counter = 0;
             $array = NULL;
             
             if ($i == 0) {
+                $query = "INSERT INTO datum VALUES ";
+                $array = &$insertDate;
+            } else if ($i == 1) {
                 $query = "INSERT INTO zaznam_cas VALUES ";
                 $array = &$insertRTT;
-            } else if ($i == 1) {
+            } else if ($i == 2) {
                 $query = "INSERT INTO zaznam VALUES ";
                 $array = &$insertRT;
             } else {
@@ -62 +65 @@
             for ($j = 0; $j < count($array); $j++) {
                 $values .= $array[$j].", ";
                 $counter++;
-                if ($counter == 500 || $j == (count($array) - 1)) {
+                if ($counter == 1000 || $j == (count($array) - 1)) {
                     $query_ = $query.substr($values, 0, strlen($values) - 2).";";
                     $stmt = $this->dbh->executeQuery($query_);
                     

cron/parser.php

@@ -105 +105 @@
             for ($i = 0; $i < 2; $i++) {
                 $this->trafficOneDay[$t->device][$i] = array();
                 for ($j = 0; $j < 11; $j++) {
-                    $this->trafficOneDay[$t->device][$i][$j] = array(0, 0); // Pocet danych vozidel, suma jejich rychlosti.
+                    $this->trafficOneDay[$t->device][$i][$j] = array(0, 0, 0); // Pocet danych vozidel, suma jejich rychlosti a pocet vozidel u kterych nesla stanovit rychlost.
                 }
             }
             
@@ -122 +122 @@
             for ($i = 0; $i < 2; $i++) {
                 $this->traffic[$t->device][$interval][$i] = array();
                 for ($j = 0; $j < 11; $j++) {
-                    $this->traffic[$t->device][$interval][$i][$j] = array(0, 0); // Pocet danych vozidel, suma jejich rychlosti.
+                    $this->traffic[$t->device][$interval][$i][$j] = array(0, 0, 0); // Pocet danych vozidel, suma jejich rychlosti a pocet vozidel u kterych nesla stanovit rychlost.
                 }
             }
         }
         
-        // Ulozeni dulezitych informaci o danem zaznamu.
-        $this->traffic[$t->device][$interval][$t->direction][$t->type10][0]++;
-        $this->traffic[$t->device][$interval][$t->direction][$t->type10][1] += $t->speed;
-        
-        // Ulozeni i do pole s prumery za cely den.
-        $this->trafficOneDay[$t->device][$t->direction][$t->type10][0]++;
-        $this->trafficOneDay[$t->device][$t->direction][$t->type10][1] += $t->speed;
+        // Ulozeni dulezitych informaci o danem zaznamu do pole s casovymi intervaly a i do pole se zaznamy za cely den.
+        if ($t->speed < 1) {
+            $this->traffic[$t->device][$interval][$t->direction][$t->type10][2]++;
+            $this->trafficOneDay[$t->device][$t->direction][$t->type10][2]++;
+        } else {
+            $this->traffic[$t->device][$interval][$t->direction][$t->type10][0]++;
+            $this->traffic[$t->device][$interval][$t->direction][$t->type10][1] += $t->speed;
+            $this->trafficOneDay[$t->device][$t->direction][$t->type10][0]++;
+            $this->trafficOneDay[$t->device][$t->direction][$t->type10][1] += $t->speed;
+        }
     }
     
     private function download($date, $zipUrl, $dir, $downloaded) {

cron/process_traffic_matrix.php

@@ -1 +1 @@
 <?php
 
-function process_traffic_matrix($parser, $traffic, $trafficOneDay, $idRecordTimeTable, $idRecordTable, $idRecordOneDayTable, &$insertRTT, &$insertRT, &$insertOneDay, $date) {
+function process_traffic_matrix($parser, $traffic, $trafficOneDay, $idDateTable, $idRecordTimeTable, $idRecordTable, $idRecordOneDayTable, &$insertDate, &$insertRTT, &$insertRT, &$insertOneDay, $date) {
     $times = array();
     
+    // Rekonstrukce casovych intervalu.
     for ($i = 0; $i < $parser->HOW_MANY_INTERVALS; $i++) {
         $times[$i] = array();
         $times[$i][0] = new DateTime($date);
@@ -23 +24 @@
         $times[$i][1]->setTime($toHours, $toMinutes, $toSeconds);
     }
     
+    // Priprava dat pro naplneni tabulek zaznam a zaznam_cas.
     foreach ($traffic as $device => $timeIntervals) {
         for ($t = 0; $t < $parser->HOW_MANY_INTERVALS; $t++) {
             if ($timeIntervals[$t] != NULL) {
@@ -30 +32 @@
                     $dataExists = FALSE;
                     
                     for ($v = 0; $v < 11; $v++) {
-                        if ($timeIntervals[$t][$d][$v][0] > 0) {
+                        $count_ = $timeIntervals[$t][$d][$v][0] + $timeIntervals[$t][$d][$v][2];
+                        if ($count_ > 0) {
                             $dataExists = TRUE;
-                            $insertRT[] = "('".$idRecordTable++."', '".$timeIntervals[$t][$d][$v][0]."', '".($timeIntervals[$t][$d][$v][1] / $timeIntervals[$t][$d][$v][0])."', '$v', '$idRecordTimeTable')";
+                            $speed_ = -1.0;
+                            
+                            if ($timeIntervals[$t][$d][$v][0] > 0) {
+                                $speed_ = $timeIntervals[$t][$d][$v][1] / (double) $timeIntervals[$t][$d][$v][0];
+                            }
+                            
+                            $insertRT[] = "('".$idRecordTable++."', '$count_', '$speed_', '$v', '$idRecordTimeTable')";
                         }
                     }
                     
                     if ($dataExists) {
-                        $insertRTT[] = "('".$idRecordTimeTable++."', '".$times[$t][0]->format("Y-m-d H:i:s.u")."', '".$times[$t][1]->format("Y-m-d H:i:s.u")."', '".($d + 1)."', '$device')";
+                        $insertRTT[] = "('".$idRecordTimeTable++."', '".($d + 1)."', '$device', '".($idDateTable + $t)."')";
                     }
                 }
             }
         }
     }
     
+    // Priprava dat pro naplneni tabulky datum. Budou vlozeny veskere casove intervaly. Je nemozne, aby neexistovalo alespon jedno zarizeni,
+    // ktere v dany casovy interval detekuje alespon jeden dopravni prostredek. I kdyby takova situace nastala, dane intervaly take mohou
+    // slouzit pro pouziti ve statistikach.
+    for ($i = 0; $i < $parser->HOW_MANY_INTERVALS; $i++) {
+        $insertDate[] = "('".$idDateTable++."', '".$times[$i][0]->format("Y-m-d H:i:s")."', '".$times[$i][1]->format("Y-m-d H:i:s")."')";
+    }
+    
+    // Priprava dat pro naplneni tabulky zaznam_prum_den.
     foreach ($trafficOneDay as $device => $direction) {
         for ($d = 0; $d < 2; $d++) {
             for ($v = 0; $v < 11; $v++) {
-                if ($direction[$d][$v][0] > 0) {
-                    $insertOneDay[] = "('".$idRecordOneDayTable++."', '".$direction[$d][$v][0]."', '".($direction[$d][$v][1] / $direction[$d][$v][0])."', '$date', '".($d + 1)."', '$device', '$v')";
+                $count_ = $direction[$d][$v][0] + $direction[$d][$v][2];
+                if ($count_ > 0) {
+                    $speed_ = -1.0;
+                    if ($direction[$d][$v][0] > 0) {
+                        $speed_ = $direction[$d][$v][1] / (double) $direction[$d][$v][0];
+                    }
+                    $insertOneDay[] = "('".$idRecordOneDayTable++."', '$count_', '$speed_', '".($d + 1)."', '$device', '$v', '$idDateTable')";
                 }
             }
         }
     }
     
+    // Priprava posledniho zaznamu ke vlozeni do tabulky datum (cely den - vzdy bude alespon jeden udaj).
+    $timeFrom = new DateTime($date);
+    $timeFrom->setTime(0, 0, 0);
+    $timeTo = new DateTime($date);
+    $timeTo->setTime(0, 0, 0);
+    $timeTo->modify("+1 day");
+    $insertDate[] = "('$idDateTable', '".$timeFrom->format("Y-m-d H:i:s")."', '".$timeTo->format("Y-m-d H:i:s")."')";
+    
 }
 
 ?>

cron/traffic.php

@@ -27 +27 @@
         // $this->intensity = $data[2];
         // $this->intensityN = $data[3];
         // $this->occupancy = $data[4];
-        $this->speed = $data[5];
-        $this->state = $data[6];
+        $this->speed = (double) $data[5];
+        $this->state = (int) $data[6];
         // $this->type = $data[7];
         // $this->duration = $data[8];
         // $this->history = $data[9];
-        $this->type10 = $data[10];
+        $this->type10 = (int) $data[10];
     }
     
 }