Revize 788fa6d8

#6351: Ulozeni dat do databaze

Pridan hlavni skript pro spusteni zpracovani dat z minuleho dne (cron.php). Pridana funkce pro pripravu dat o lokacich a o doprave pro ulozeni do databaze (process_traffic_matrix.php). Pridany funkce pro ulozeni dat do databaze (dao.php).

cron/cron.php

@@ +1 @@
+<?php
+
+require_once "dao/dao.php";
+require_once "db/db-web.php";
+require_once "parser.php";
+require_once "process_traffic_matrix.php";
+
+function cron() {
+    $dbh = new DB_WEB();
+    $DAO = new DAO();
+    $DAO->setDB($dbh);
+    
+    $date = new DateTime();
+    $date->modify("-1 day");
+    
+    if ($DAO->controlTrafficData($date->format("Y-m-d"))) {
+        $parser = new Parser();
+        $parser->doWork($date->format("Ymd"));
+        
+        $traffic = $parser->getTraffic();
+        $locations = $parser->getLocations();
+        
+        $DAO->insertVehicles(); // Pokud nejsou typy vozidel v tabulce - pridat.
+        
+        // Pridat udaje o novych zarizenich.
+        foreach ($locations as $l) {
+            $DAO->insertLocationData($l);
+        }
+        
+        // Pridat zaznamy z vybraneho dne.
+        $insertRTT = array();
+        $insertRT = array();
+        process_traffic_matrix($parser, $traffic, $DAO->findFirstId("zaznam_cas"), $DAO->findFirstId("zaznam"), $insertRTT, $insertRT, $date->format("Y-m-d"));
+        $DAO->insertTrafficData($insertRTT, $insertRT);
+    }
+}
+
+?>

cron/dao/dao.php

@@ -1 +1 @@
 <?php
 
 require_once "db/db-web.php";
+require_once "location.php";
 
 class DAO {
   
     protected $dbh;
-    
+  
     public function setDB($dbh) {
         $this->dbh = $dbh;
     }
     
+    public function insertVehicles() {
+        $query = "SELECT COUNT(*) AS total FROM vozidla";
+        $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 ";
+            $query .= "('0', 'Neznámé vozidlo'), ";
+            $query .= "('1', 'Motocykl'), ";
+            $query .= "('2', 'Auto'), ";
+            $query .= "('3', 'Auto s přívěsem'), ";
+            $query .= "('4', 'Dodávka'), ";
+            $query .= "('5', 'Dodávka s přívěsem'), ";
+            $query .= "('6', 'Lehký nákladní automobil'), ";
+            $query .= "('7', 'Lehký nákladní automobil s přívěsem'), ";
+            $query .= "('8', 'Nákladní automobil'), ";
+            $query .= "('9', 'Nákladní automobil s přívěsem'), ";
+            $query .= "('10', 'Autobus');";
+            $stmt = $this->dbh->executeQuery($query);
+        }
+    }
+    
+    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')."';";
+        $stmt = $this->dbh->executeQuery($query);
+        return $stmt->fetchAssoc()["total"] < 1;
+    }
+    
+    public function insertTrafficData($insertRTT, $insertRT) {
+        for ($i = 0; $i < 2; $i++) {
+            $query = "";
+            $values = "";
+            $counter = 0;
+            $array = NULL;
+            
+            if ($i == 0) {
+                $query = "INSERT INTO zaznam_cas VALUES ";
+                $array = &$insertRTT;
+            } else {
+                $query = "INSERT INTO zaznam VALUES ";
+                $array = &$insertRT;
+            }
+            
+            for ($j = 0; $j < count($array); $j++) {
+                $values .= $array[$j].", ";
+                $counter++;
+                if ($counter == 500 || $j == (count($array) - 1)) {
+                    $query_ = $query.substr($values, 0, strlen($values) - 2).";";
+                    $stmt = $this->dbh->executeQuery($query_);
+                    
+                    $values = "";
+                    $counter = 0;
+                }
+            }
+        }
+    }
+    
+    public function insertLocationData($location) {
+        
+        // --- Kontrola, zda je mesto v tabulce, pripadne jeho pridani. ---
+        
+        $townId = $this->conditionalInsertion($location, "mesto", -1);
+        
+        // --- Kontrola, zda je ulice v tabulce, pripadne jeji pridani. ---
+        
+        $streetId = $this->conditionalInsertion($location, "ulice", $townId);
+        
+        // --- Kontrola, zda je zarizeni v tabulce, pripadne jeho pridani. ---
+        
+        $query = "SELECT * FROM zarizeni WHERE id='".$location->device."';";
+        $stmt = $this->dbh->executeQuery($query);
+        
+        if (!($stmt->fetchAssoc())) {
+            // Zarizeni se v tabulce jeste nenachazi.
+            $query = "INSERT INTO zarizeni VALUES ('".$location->device."', '".$location->name."', '0', '$streetId');";
+            $stmt = $this->dbh->executeQuery($query);
+        }
+        
+    }
+    
+    // Pro mesta a ulice.
+    private function conditionalInsertion($location, $table, $townId) {
+        $query = "SELECT id FROM $table WHERE nazev=";
+        if ($townId < 0) {
+            // Hledame mesto.
+            $query .= "'".$location->town."';";
+        } else {
+            // Hledame ulici (s odkazem na konkretni mesto - ruzna mesta mohou mit shodny nazev nejake ulice).
+            $query .= "'".$location->street."' AND mesto_id='$townId';";
+        }
+        $stmt = $this->dbh->executeQuery($query);
+        
+        $id = -1;
+        if ($row = $stmt->fetchAssoc()) {
+            // Zaznam uz je v tabulce.
+            $id = $row["id"];
+        } else {
+            // Zaznam se v tabulce jeste nenachazi - nalezeni id pro novy zaznam.
+            $id = $this->findFirstId($table);
+            
+            $query = "INSERT INTO $table VALUES ";
+            if ($townId < 0) {
+                $query .= "('$id', '".$location->town."');";
+            } else {
+                $query .= "('$id', '".$location->street."', '$townId');";
+            }
+            
+            // Vlozeni zaznamu do tabulky.
+            $stmt = $this->dbh->executeQuery($query);
+        }
+        
+        return $id;
+    }
+    
+    public function findFirstId($table) {
+        $query = "SELECT id FROM $table ORDER BY id DESC LIMIT 1;";
+        $stmt = $this->dbh->executeQuery($query);
+        
+        $id = -1;
+        if ($row = $stmt->fetchAssoc()) {
+            // V tabulce je vice zaznamu.
+            $id = $row["id"] + 1;
+        } else {
+            // Tabulka je prazdna.
+            $id = 1;
+        }
+        
+        return $id;
+    }
+    
 }
 
 ?>

cron/parser.php

@@ -6 +6 @@
 class Parser {
     
     // Pro data o doprave - na kolik intervalu se ma rozdelit den (96 = 4 * 24 => intervaly po ctvrt hodine).
-    private $HOW_MANY_INTERVALS = 96;
+    public $HOW_MANY_INTERVALS = 96;
     
     // Pro data o doprave - jak dlouho v milisekundach trva jeden interval.
-    private $intervalMilli;
+    public $intervalMilli;
     
     private $name;
     private $path;

cron/process_traffic_matrix.php

@@ +1 @@
+<?php
+
+function process_traffic_matrix($parser, $traffic, $idRecordTimeTable, $idRecordTable, &$insertRTT, &$insertRT, $date) {
+    $times = array();
+    
+    for ($i = 0; $i < $parser->HOW_MANY_INTERVALS; $i++) {
+        $times[$i] = array();
+        $times[$i][0] = new DateTime($date);
+        $times[$i][1] = new DateTime($date);
+        
+        $fromSec = (int) (($i * $parser->intervalMilli) / 1000);
+        $toSec = (int) (($i + 1) * $parser->intervalMilli / 1000);
+        
+        $fromHours = (int) ($fromSec / 3600);
+        $fromMinutes = (int) (($fromSec - $fromHours * 3600) / 60);
+        $fromSeconds = (int) ($fromSec - $fromHours * 3600 - $fromMinutes * 60);
+        
+        $toHours = (int) ($toSec / 3600);
+        $toMinutes = (int) (($toSec - $toHours * 3600) / 60);
+        $toSeconds = (int) ($toSec - $toHours * 3600 - $toMinutes * 60);
+        
+        $times[$i][0]->setTime($fromHours, $fromMinutes, $fromSeconds);
+        $times[$i][1]->setTime($toHours, $toMinutes, $toSeconds);
+    }
+    
+    foreach ($traffic as $device => $timeIntervals) {
+        for ($t = 0; $t < $parser->HOW_MANY_INTERVALS; $t++) {
+            if ($timeIntervals[$t] != NULL) {
+                for ($d = 0; $d < 2; $d++) {
+                    $dataExists = FALSE;
+                    
+                    for ($v = 0; $v < 11; $v++) {
+                        if ($timeIntervals[$t][$d][$v][0] > 0) {
+                            $dataExists = TRUE;
+                            $insertRT[] = "('".$idRecordTable++."', '".$timeIntervals[$t][$d][$v][0]."', '".($timeIntervals[$t][$d][$v][1] / $timeIntervals[$t][$d][$v][0])."', '$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')";
+                    }
+                }
+            }
+        }
+    }
+}
+
+?>