Revize bad470c9

refs #7029: Vytvoreni testu na zpracovani zaznamu o doprave

Vytvoreni testu na komplikovanou cast cronu (zpracovani konkretnich zaznamu o doprave). Ostatni casti cronu otestovany zkousenim aplikace.

cron_test/READ_ME.TXT

@@ +1 @@
+Vime, ze stazeni souboru, extrahovani a odstraneni jde (otestovany ruzne varianty, detaily do logu).
+To same komunikace s databazi, pocatecni inicializaci databaze (vlozeni typu vozidel), apod.
+Jedine, cim si nejsme jisti je zpracovani zaznamu (jedna se o velke mnozstvi dat a o slozity algoritmus zpracovani).
+Ukolem tohoto testovani je tedy zjistit, jestli se zaznamy o doprave spravne zpracovavaji.

cron_test/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() {
+    // Nacteni dat z testovacich souboru.
+    cron_procedure(new DateTime("20180605"));
+}
+
+// Funkce, ktera je volana bud cron() nebo year_cron() a ktera stahne data pro dany den.
+function cron_procedure($date) {
+    // Kvuli timeoutu.
+    set_time_limit(0);
+    
+    // Objekt pro logovani.
+    $logs = new Logging();
+    
+    // Vytvoreni objektu pro komunikaci s DB.
+    $dbh = new DB_WEB($logs);
+    $DAO = new DAO();
+    $DAO->setDB($dbh);
+    
+    if ($date != NULL && $DAO->controlTrafficData($date->format("Y-m-d"))) {
+        $logs->log(Logging::INFO, "Data k vybranemu dni (".$date->format("d.m.Y").") jeste v databazi nejsou.");
+        $parser = new Parser($logs);
+        $parser->doWork($date->format("Ymd"));
+        
+        $traffic = $parser->getTraffic();
+        $trafficOneDay = $parser->getTrafficOneDay();
+        $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.
+        $insertDate = array();
+        $insertRTT = array();
+        $insertRT = array();
+        $insertOneDay = array();
+        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_test/dao/dao.php

@@ +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 vozidlo";
+        $stmt = $this->dbh->executeQuery($query);
+        
+        if ($stmt->fetchAssoc()["total"] < 1) {
+            // 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'), ";
+            $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 datum WHERE od >= '$dateStr' AND od < '".$dateTo->format('Y-m-d')."';";
+        $stmt = $this->dbh->executeQuery($query);
+        return $stmt->fetchAssoc()["total"] < 1;
+    }
+    
+    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 == 2) {
+                $query = "INSERT INTO zaznam VALUES ";
+                $array = &$insertRT;
+            } else {
+                $query = "INSERT INTO zaznam_prum_den VALUES ";
+                $array = &$insertOneDay;
+            }
+            
+            for ($j = 0; $j < count($array); $j++) {
+                $values .= $array[$j].", ";
+                $counter++;
+                if ($counter == 1000 || $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 {
+                // Jestlize ulice v DB jeste neexistuje, nastavit geolokaci (nema smysl zem. sirku a delku zjistovat driv - nejedna se o dulezite informace pro CRON).
+                $location->setGeolocation();
+                $query .= "('$id', '".$location->street."', '$townId', '".$location->lat."', '".$location->lng."');";
+            }
+            
+            // 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_test/data/READ_ME.TXT

@@ +1 @@
+// Obsah souboru "test_Ymd_doprava.csv"
+idDetektor - z tohoto udaje "10<ID_ZARIZENI>10<SMER>" bereme oznaceni zarizeni (detektoru) a smer, ve kterem snima (1 = ve smeru, 2 = v proti smeru).
+DatumCas - datum a cas zaznamu.
+Intenzita - NEPOTREBNE, NAHRAZENO PISMENEM "X".
+IntenzitaN - NEPOTREBNE, NAHRAZENO PISMENEM "X".
+Obsazenost - NEPOTREBNE, NAHRAZENO PISMENEM "X".
+Rychlost - namerena rychlost (musi se brat v potaz i zaznamy s rychlosti 0.0 = rychlost nesla urcit).
+Stav - jestli je zarizeni funkcni (=1) nebo nefunkcni (<0). V aktualni verzi se s timto udajem nepracuje (pokud nam zarizeni da rozumny udaj, je nam jedno, jestli je funkcni nebo ne - do budoucna by chtelo opravit udaj v DB).
+TypVozidla - NEPOTREBNE, NAHRAZENO PISMENEM "X".
+Trvani100 - NEPOTREBNE, NAHRAZENO PISMENEM "X".
+RychlostHistorie - NEPOTREBNE, NAHRAZENO PISMENEM "X".
+TypVozidla10 - konkretni typ vozidla (s obecnym oznacenim, 4 skupiny, nepracujeme).
+
+
+
+// Obsah souboru "test_Ymd_lokace.csv"
+Name - popis ulice (napr. z jakeho smeru zarizeni snima).
+Town - nazev mesta.
+Street - nazev ulice.
+IdDevice - KP<ID_ZARIZENI>, bereme oznaceni detektoru.
+idArea - NEPOTREBNE, NAHRAZENO PISMENEM "X".
+
+
+
+Jednotlive sloupce v obou souborech jsou oddeleny svislitkem.

cron_test/data/test_20180605_doprava.csv

@@ +1 @@
[phone]|"2018-06-05 00:00:00.123"|X|X|X|17.000|0|X|X|X|8
[phone]|"2018-06-05 00:00:01.546"|X|X|X|12.000|0|X|X|X|0
[phone]|"2018-06-05 00:00:02.849"|X|X|X|68.000|0|X|X|X|2
[phone]|"2018-06-05 00:00:03.048"|X|X|X|168.92|0|X|X|X|2
[phone]|"2018-06-05 00:00:10.000"|X|X|X|94.557|0|X|X|X|2
[phone]|"2018-06-05 00:00:12.804"|X|X|X|34.000|0|X|X|X|8
[phone]|"2018-06-05 00:00:13.040"|X|X|X|00.000|0|X|X|X|2
[phone]|"2018-06-05 00:00:14.144"|X|X|X|00.000|0|X|X|X|3
[phone]|"2018-06-05 00:00:15.597"|X|X|X|16.000|0|X|X|X|1
[phone]|"2018-06-05 00:14:59.999"|X|X|X|19.050|0|X|X|X|4
[phone]|"2018-06-05 00:15:00.000"|X|X|X|25.150|0|X|X|X|2
[phone]|"2018-06-05 00:15:52.324"|X|X|X|65.400|0|X|X|X|2
[phone]|"2018-06-05 00:00:00.123"|X|X|X|17.000|0|X|X|X|2
[phone]|"2018-06-05 00:00:01.546"|X|X|X|12.000|0|X|X|X|0
[phone]|"2018-06-05 00:00:02.849"|X|X|X|68.000|0|X|X|X|8
[phone]|"2018-06-05 00:00:03.048"|X|X|X|168.92|0|X|X|X|8
[phone]|"2018-06-05 00:00:10.000"|X|X|X|94.557|0|X|X|X|8
[phone]|"2018-06-05 00:00:12.804"|X|X|X|34.000|0|X|X|X|2
[phone]|"2018-06-05 00:00:13.040"|X|X|X|00.000|0|X|X|X|8
[phone]|"2018-06-05 00:00:14.144"|X|X|X|00.000|0|X|X|X|3
[phone]|"2018-06-05 00:00:15.597"|X|X|X|16.000|0|X|X|X|1
[phone]|"2018-06-05 00:14:59.999"|X|X|X|19.050|0|X|X|X|4
[phone]|"2018-06-05 00:15:00.000"|X|X|X|25.150|0|X|X|X|8
[phone]|"2018-06-05 00:25:46.059"|X|X|X|65.789|0|X|X|X|6

cron_test/data/test_20180605_lokace.csv

@@ +1 @@
+"Česká Kubice, směr od Německa"|"Česká Kubice"|"Česká Kubice"|"KP055"|X
+"Česká Kubice, směr od Babylonu"|"Česká Kubice"|"Česká Kubice"|"KP056"|X

cron_test/db/db-exception.php

@@ +1 @@
+<?php
+
+require_once "/../logging.php";
+
+class DB_Exception extends Exception {
+    
+    public function __construct($logs = NULL, $message = false, $code = false) {
+        if (!$message) {
+            $this->message = mysql_error();
+        } 
+        if (!$code) {
+            $this->code = mysql_errno();
+        }
+        if ($logs != NULL) {
+            $logs->log(Logging::ERROR, $this->__toString());
+        }
+    }
+    
+}
+
+?>

cron_test/db/db-pdo.php

@@ +1 @@
+<?php
+
+require_once "db-exception.php";
+
+class DB_PDO {
+  
+    protected $user;
+    protected $pass;
+    protected $dbhost;
+    protected $dbname;
+    protected $dbh;
+    
+    protected $logs;
+  
+    public function __construct($user, $pass, $dbhost, $dbname) {
+        $this->user = $user;
+        $this->pass = $pass;
+        $this->dbhost = $dbhost;
+        $this->dbname = $dbname;
+    }
+  
+    protected function connect() {
+        $this->dbh = new PDO("mysql:host=".$this->dbhost.";dbname=".$this->dbname.";charset=utf8", $this->user, $this->pass);
+        if (!$this->dbh) {
+            throw new DB_Exception($this->logs);
+        }
+    }
+  
+    public function executeQuery($query) {
+        if (!$this->dbh) {
+            $this->connect();
+        }
+        $ret = $this->dbh->query($query);
+        if (!$ret) {
+            throw new DB_Exception($this->logs);
+        } else {
+            $stmt = new DB_PDOStatement($this->dbh, $query, $this->logs);
+            $stmt->result = $ret;
+            $stmt->number = $ret->rowCount();
+            return $stmt;
+        }
+    }
+      
+}
+
+class DB_PDOStatement {
+    
+    public $result;
+    public $query;
+    public $number;
+    protected $dbh;
+    
+    private $logs;
+  
+    public function __construct($dbh, $query, $logs) {
+        $this->query = $query;
+        $this->dbh = $dbh;
+        $this->logs = $logs;
+        if (!$dbh) {
+            throw new DB_Exception($this->logs, "Spojení s databází se nezdařilo!");
+        }
+    }
+  
+    public function fetchAssoc() {
+        if (!$this->result) {
+            throw new DB_Exception($this->logs, "Dotaz nebyl vykonán!");
+        }
+        return $this->result->fetch(PDO::FETCH_ASSOC);
+    }
+    
+}
+
+?>

cron_test/db/db-web.php

@@ +1 @@
+<?php
+
+require_once "db-pdo.php";
+
+class DB_WEB extends DB_PDO {
+  
+    protected $user = "root";
+    protected $pass = "";
+    protected $dbhost = "localhost";
+    protected $dbname = "prujezd_vozidel";
+    
+    protected $logs = NULL;
+  
+    public function __construct($logs) {
+        $this->logs = $logs;
+    }
+     
+}
+
+?>

cron_test/empty_database.sql

@@ +1 @@
+-- phpMyAdmin SQL Dump
+-- version 3.5.2.2
+-- http://www.phpmyadmin.net
+--
+-- Počítač: 127.0.0.1
+-- Vygenerováno: Čtv 07. čen 2018, 00:14
+-- Verze MySQL: 5.5.27-log
+-- Verze PHP: 5.4.6
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+SET time_zone = "+00:00";
+
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+
+--
+-- Databáze: `prujezd_vozidel`
+--
+CREATE DATABASE `prujezd_vozidel` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+USE `prujezd_vozidel`;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `datum`
+--
+
+CREATE TABLE IF NOT EXISTS `datum` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `od` datetime NOT NULL,
+  `do` datetime NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `mesto`
+--
+
+CREATE TABLE IF NOT EXISTS `mesto` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `nazev` varchar(100) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `ulice`
+--
+
+CREATE TABLE IF NOT EXISTS `ulice` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `nazev` varchar(255) NOT NULL,
+  `mesto_id` bigint(20) NOT NULL,
+  `zem_sirka` double NOT NULL DEFAULT '-1' COMMENT 'Zemepisna sirka podle GOOGLE.',
+  `zem_delka` double NOT NULL DEFAULT '-1' COMMENT 'Zemepisna delka podle GOOGLE.',
+  PRIMARY KEY (`id`),
+  KEY `fk_ulice_mesto_idx` (`mesto_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `vozidlo`
+--
+
+CREATE TABLE IF NOT EXISTS `vozidlo` (
+  `id` bigint(20) NOT NULL COMMENT 'Odpovídá číslu skupiny vozidla (TypVozidla10 v csv souboru s daty). Hodnoty 0-10.',
+  `nazev` varchar(45) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zarizeni`
+--
+
+CREATE TABLE IF NOT EXISTS `zarizeni` (
+  `id` varchar(20) NOT NULL COMMENT 'Odpovídá idDevice v location.csv.',
+  `smer_popis` varchar(255) NOT NULL COMMENT 'Odpovídá Name v locations.csv.',
+  `stav` int(11) NOT NULL,
+  `ulice_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `fk_zarizeni_ulice1_idx` (`ulice_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zaznam`
+--
+
+CREATE TABLE IF NOT EXISTS `zaznam` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `vozidla_pocet` int(11) NOT NULL,
+  `rychlost_prumer` double NOT NULL,
+  `vozidla_id` bigint(20) NOT NULL,
+  `zaznam_cas_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `fk_zaznam_vozidla1_idx` (`vozidla_id`),
+  KEY `fk_zaznam_zaznam_cas1_idx` (`zaznam_cas_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zaznam_cas`
+--
+
+CREATE TABLE IF NOT EXISTS `zaznam_cas` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `smer` int(11) NOT NULL COMMENT '1 nebo 2 viz struktura idDetektor v csv souboru s daty.',
+  `zarizeni_id` varchar(20) NOT NULL,
+  `datum_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `fk_zaznam_cas_zarizeni1_idx` (`zarizeni_id`),
+  KEY `datum_id` (`datum_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zaznam_prum_den`
+--
+
+CREATE TABLE IF NOT EXISTS `zaznam_prum_den` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `vozidla_pocet` int(11) NOT NULL,
+  `rychlost_prumer` double NOT NULL,
+  `smer` int(11) NOT NULL,
+  `zarizeni_id` varchar(20) NOT NULL,
+  `vozidla_id` bigint(20) NOT NULL,
+  `datum_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `zarizeni_id` (`zarizeni_id`),
+  KEY `vozidla_id` (`vozidla_id`),
+  KEY `datum_id` (`datum_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
+
+--
+-- Omezení pro exportované tabulky
+--
+
+--
+-- Omezení pro tabulku `ulice`
+--
+ALTER TABLE `ulice`
+  ADD CONSTRAINT `fk_ulice_mesto` FOREIGN KEY (`mesto_id`) REFERENCES `mesto` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zarizeni`
+--
+ALTER TABLE `zarizeni`
+  ADD CONSTRAINT `fk_zarizeni_ulice1` FOREIGN KEY (`ulice_id`) REFERENCES `ulice` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zaznam`
+--
+ALTER TABLE `zaznam`
+  ADD CONSTRAINT `fk_zaznam_vozidla1` FOREIGN KEY (`vozidla_id`) REFERENCES `vozidlo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `fk_zaznam_zaznam_cas1` FOREIGN KEY (`zaznam_cas_id`) REFERENCES `zaznam_cas` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zaznam_cas`
+--
+ALTER TABLE `zaznam_cas`
+  ADD CONSTRAINT `zaznam_cas_ibfk_1` FOREIGN KEY (`datum_id`) REFERENCES `datum` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `fk_zaznam_cas_zarizeni1` FOREIGN KEY (`zarizeni_id`) REFERENCES `zarizeni` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zaznam_prum_den`
+--
+ALTER TABLE `zaznam_prum_den`
+  ADD CONSTRAINT `zaznam_prum_den_ibfk_3` FOREIGN KEY (`datum_id`) REFERENCES `datum` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `zaznam_prum_den_ibfk_1` FOREIGN KEY (`zarizeni_id`) REFERENCES `zarizeni` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `zaznam_prum_den_ibfk_2` FOREIGN KEY (`vozidla_id`) REFERENCES `vozidlo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

cron_test/index.php

@@ +1 @@
+<?php
+
+require_once "cron.php";
+require_once "logging.php";
+
+echo "<h1>Zkouška cronu</h1>";
+echo "<p>Spouštím script:</p>";
+
+cron();
+
+echo "<p>KONEC</p>";
+
+?>

cron_test/location.php

@@ +1 @@
+<?php
+
+class Location {
+
+    // Pouzivane atributy.
+    public $name;
+    public $town;
+    public $street;
+    public $device;
+    
+    // Nepouzivane.
+    // public $area;
+    
+    // --- ZEMEPISNA SIRKA A DELKA A UDAJE POTREBNE K JEJICH ZJISTENI. ---
+    
+    private $key;
+    private $locality;
+    private $region;
+    
+    public $lat;
+    public $lng;
+    
+    public function __construct($data) {
+        $this->name = $data[0];
+        $this->town = $data[1];
+        $this->street = $data[2];
+        $this->device = substr($data[3], 2);
+        // $this->area = $data[4];
+        
+        $this->key = "AIzaSyCSx7hyAzQiG5uocJTeZgf1Z3lpDy4kpEk";
+        $this->locality = "Plzeňský kraj";
+        $this->region = "cz";
+        $this->lat = -1;
+        $this->lng = -1;
+    }
+    
+    // V pripade problemu, se ziskanim souboru protokolem HTTPS, v php.ini odkomentovat "extension=php_openssl.dll".
+    public function setGeolocation() {
+        $address = $this->town;
+        if ($this->town != $this->street) {
+            $address .= " ".$this->street;
+        }
+        $address .= " ".$this->locality;
+        $address = str_replace(" ", "+", $address); // Nemusi byt, jen pro jistotu.
+        
+        $json = file_get_contents("https://maps.google.com/maps/api/geocode/json?address=$address&sensor=false&region=".$this->region."&key=".$this->key);
+        $json = json_decode($json, TRUE);
+        if ($json["status"] == "OK") {
+            $this->lat = $json["results"]["0"]["geometry"]["location"]["lat"];
+            $this->lng = $json["results"]["0"]["geometry"]["location"]["lng"];
+        } else {
+            $this->lat = -1;
+            $this->lng = -1;
+        }
+    }
+    
+}
+
+?>

cron_test/log/cron.txt

@@ +1 @@
+07.06.2018 00:16:03.043
+--- INFO ---
+Data k vybranemu dni (05.06.2018) jeste v databazi nejsou.
+
+07.06.2018 00:16:03.070
+--- INFO ---
+ZACATEK PROCEDURY pro den 05.06.2018.
+
+07.06.2018 00:16:03.077
+--- INFO ---
+Zpracovavani zaznamu o doprave.
+
+07.06.2018 00:16:03.086
+--- INFO ---
+Zpracovavani zaznamu o lokacich.
+
+07.06.2018 00:16:03.172
+--- INFO ---
+KONEC PROCEDURY.
+
+07.06.2018 00:28:23.291
+--- INFO ---
+Data k vybranemu dni (05.06.2018) jeste v databazi nejsou.
+
+07.06.2018 00:28:23.306
+--- INFO ---
+ZACATEK PROCEDURY pro den 05.06.2018.
+
+07.06.2018 00:28:23.317
+--- INFO ---
+Zpracovavani zaznamu o doprave.
+
+07.06.2018 00:28:23.398
+--- INFO ---
+Zpracovavani zaznamu o lokacich.
+
+07.06.2018 00:28:23.409
+--- INFO ---
+KONEC PROCEDURY.
+
+07.06.2018 00:35:30.813
+--- INFO ---
+Data k vybranemu dni (05.06.2018) jeste v databazi nejsou.
+
+07.06.2018 00:35:30.829
+--- INFO ---
+ZACATEK PROCEDURY pro den 05.06.2018.
+
+07.06.2018 00:35:30.838
+--- INFO ---
+Zpracovavani zaznamu o doprave.
+
+07.06.2018 00:35:30.851
+--- INFO ---
+Zpracovavani zaznamu o lokacich.
+
+07.06.2018 00:35:30.861
+--- INFO ---
+KONEC PROCEDURY.
+

cron_test/logging.php

@@ +1 @@
+<?php
+
+class Logging {
+    
+    // Zpravy jsou pouze informativni.
+    const INFO = 0;
+    // Jedna se o mene zavazne stavy - treba data pro dany den jeste neexistuji.
+    const WARNING = 1;
+    // Doslo k padu cele procedury - napr. nepovedlo se vytvorit slozku pro extrahovana data.
+    const ERROR = 2;
+    
+    // Soubor, do ktereho se budou zaznamy vkladat (defaultne ve slozce log).
+    private $log_file;
+    
+    public function __construct() {
+        $this->log_file = "log/cron.txt";
+    }
+    
+    public function log($type, $message) {
+        $type_str = "";
+        switch ($type) {
+            case self::INFO: $type_str = "INFO"; break;
+            case self::WARNING: $type_str = "WARNING"; break;
+            default: $type_str = "ERROR"; break;
+        }
+        
+        $micro_date = microtime();
+        $date_array = explode(" ", $micro_date);
+        $date = sprintf("%s.%03d", date("d.m.Y H:i:s", $date_array[1]), (int) ($date_array[0] * 1000));
+        
+        file_put_contents($this->log_file, "$date\r\n--- $type_str ---\r\n$message\r\n\r\n", FILE_APPEND);
+    }
+    
+}
+
+?>

cron_test/parser.php

@@ +1 @@
+<?php
+
+require_once "traffic.php";
+require_once "location.php";
+require_once "logging.php";
+
+class Parser {
+    
+    // Pro data o doprave - na kolik intervalu se ma rozdelit den (96 = 4 * 24 => intervaly po ctvrt hodine).
+    public $HOW_MANY_INTERVALS = 96;
+    
+    // Pro data o doprave - jak dlouho v milisekundach trva jeden interval.
+    public $intervalMilli;
+    
+    // Pole informaci o lokacich.
+    private $locations;
+    
+    // Pole informaci o doprave - peti-rozmerne pole s nasledujici strukturou:
+    // Zaklad tvori pole zarizeni. Pro kazde zarizeni je pak pole pro casove intervaly.
+    // Kazdy casovy interval ma pole o dvou prvcich (dva smery). Pro kazdy smer je vytvoreno pole o jedenacti
+    // prvcich (jedenact typu vozidel). Pro kazde vozidlo je vytvoreno pole o dvou prvcich (pocet vozidel, suma rychlosti).
+    private $traffic;
+    
+    // Skoro to same jako $traffic, akorat bez rozmeru pro casove intervaly.
+    private $trafficOneDay;
+    
+    // Objekt pro logovani do souboru cron.txt ve slozce log.
+    private $logs;
+    
+    public function __construct($logs) {
+        // Pro lokace se data predzpracovavat nemusi.
+        $this->locations = array();
+        
+        // Naopak u zaznamu je prilis zbytecnych informaci - k predzpracovani dojit musi.
+        $this->intervalMilli = (int) (24 * 3600000 / $this->HOW_MANY_INTERVALS);
+        $this->traffic = array();
+        $this->trafficOneDay = array();
+        
+        $this->logs = $logs;
+    }
+    
+    public function doWork($date) {
+        // Stazeni archivu pri stestovani neresime.
+        // Pouze zapis do logu a zavolani zpracovani obou souboru.
+        $this->logs->log(Logging::INFO, "ZACATEK PROCEDURY pro den ".DateTime::createFromFormat("Ymd", $date)->format("d.m.Y").".");
+        $this->logs->log(Logging::INFO, "Zpracovavani zaznamu o doprave.");
+        $this->parse("data/test_".$date."_doprava.csv", TRUE);
+        $this->logs->log(Logging::INFO, "Zpracovavani zaznamu o lokacich.");
+        $this->parse("data/test_".$date."_lokace.csv", FALSE);
+        $this->logs->log(Logging::INFO, "KONEC PROCEDURY.");
+    }
+    
+    private function parse($fileName, $traffic) {
+        if (($file = fopen($fileName, "r"))) {
+            while (($row = fgetcsv($file, 1000, "|"))) {
+                if ($traffic) {
+                    $this->saveVehicleInfo(new Traffic($row));
+                } else {
+                    $this->locations[] = new Location($row);
+                }
+            }
+            fclose($file);
+        }
+    }
+    
+    private function saveVehicleInfo($t) {
+        // Kontrola, jestli je pro dane zarizeni vytvorene pole casu.
+        if (!isSet($this->traffic[$t->device])) {
+            // Vytvorit prvni dva rozmery pole pro dopravni data s rozdelenim na casove intervaly.
+            $this->traffic[$t->device] = array();
+            for ($i = 0; $i < $this->HOW_MANY_INTERVALS; $i++) {
+                $this->traffic[$t->device][$i] = NULL;
+            }
+            
+            // U pole s prumery za cely den rovnou vytvorit vsechny rozmery.
+            $this->trafficOneDay[$t->device] = array();
+            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, 0); // Pocet danych vozidel, suma jejich rychlosti a pocet vozidel u kterych nesla stanovit rychlost.
+                }
+            }
+            
+        }
+        
+        // Zjisteni, do jakeho casoveho intervalu patri zaznam.
+        list($date, $time) = explode(" ", $t->dateTime->format("Y-m-d H:i:s.u"), 2);
+        list($hours, $minutes, $seconds) = explode(":", $time, 3);
+        $interval = (int) (($hours * 3600000 + $minutes * 60000 + $seconds * 1000) / $this->intervalMilli);
+        
+        // Kontrola, jestli je pro dany casovy interval vytvorene pole pro smery.
+        if ($this->traffic[$t->device][$interval] == NULL) {
+            $this->traffic[$t->device][$interval] = array();
+            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, 0); // Pocet danych vozidel, suma jejich rychlosti a pocet vozidel u kterych nesla stanovit rychlost.
+                }
+            }
+        }
+        
+        // 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;
+        }
+    }
+    
+    public function getTraffic() {
+        return $this->traffic;
+    }
+    
+    public function getTrafficOneDay() {
+        return $this->trafficOneDay;
+    }
+    
+    public function getLocations() {
+        return $this->locations;
+    }
+    
+}
+
+?>

cron_test/process_traffic_matrix.php

@@ +1 @@
+<?php
+
+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);
+        $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);
+    }
+    
+    // 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) {
+                for ($d = 0; $d < 2; $d++) {
+                    $dataExists = FALSE;
+                    
+                    for ($v = 0; $v < 11; $v++) {
+                        $count_ = $timeIntervals[$t][$d][$v][0] + $timeIntervals[$t][$d][$v][2];
+                        if ($count_ > 0) {
+                            $dataExists = TRUE;
+                            $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++."', '".($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++) {
+                $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_test/traffic.php

@@ +1 @@
+<?php
+
+class Traffic {
+    
+    // Pouzivane atributy.
+    public $device;
+    public $direction;
+    public $dateTime;
+    public $speed;
+    public $type10;
+    
+    // TODO pouziti.
+    public $state;
+    
+    // Nepouzivane.
+    // public $intensity;
+    // public $intensityN;
+    // public $occupancy;
+    // public $type;
+    // public $duration;
+    // public $history;
+    
+    public function __construct($data) {
+        $this->device = substr($data[0], 2, 3);
+        $this->direction = substr($data[0], 7) - 1; // Misto hodnot 1, 2 - hodnoty 0, 1 (kvuli poli).
+        $this->dateTime = new DateTime($data[1]);
+        // $this->intensity = $data[2];
+        // $this->intensityN = $data[3];
+        // $this->occupancy = $data[4];
+        $this->speed = (double) $data[5];
+        $this->state = (int) $data[6];
+        // $this->type = $data[7];
+        // $this->duration = $data[8];
+        // $this->history = $data[9];
+        $this->type10 = (int) $data[10];
+    }
+    
+}
+
+?>

cron_test/vystup.sql

@@ +1 @@
+-- phpMyAdmin SQL Dump
+-- version 3.5.2.2
+-- http://www.phpmyadmin.net
+--
+-- Počítač: 127.0.0.1
+-- Vygenerováno: Čtv 07. čen 2018, 01:11
+-- Verze MySQL: 5.5.27-log
+-- Verze PHP: 5.4.6
+
+SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
+SET time_zone = "+00:00";
+
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+
+--
+-- Databáze: `prujezd_vozidel`
+--
+CREATE DATABASE `prujezd_vozidel` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+USE `prujezd_vozidel`;
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `datum`
+--
+
+CREATE TABLE IF NOT EXISTS `datum` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `od` datetime NOT NULL,
+  `do` datetime NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=98 ;
+
+--
+-- Vypisuji data pro tabulku `datum`
+--
+
+INSERT INTO `datum` (`id`, `od`, `do`) VALUES
+(1, '2018-06-05 00:00:00', '2018-06-05 00:15:00'),
+(2, '2018-06-05 00:15:00', '2018-06-05 00:30:00'),
+(3, '2018-06-05 00:30:00', '2018-06-05 00:45:00'),
+(4, '2018-06-05 00:45:00', '2018-06-05 01:00:00'),
+(5, '2018-06-05 01:00:00', '2018-06-05 01:15:00'),
+(6, '2018-06-05 01:15:00', '2018-06-05 01:30:00'),
+(7, '2018-06-05 01:30:00', '2018-06-05 01:45:00'),
+(8, '2018-06-05 01:45:00', '2018-06-05 02:00:00'),
+(9, '2018-06-05 02:00:00', '2018-06-05 02:15:00'),
+(10, '2018-06-05 02:15:00', '2018-06-05 02:30:00'),
+(11, '2018-06-05 02:30:00', '2018-06-05 02:45:00'),
+(12, '2018-06-05 02:45:00', '2018-06-05 03:00:00'),
+(13, '2018-06-05 03:00:00', '2018-06-05 03:15:00'),
+(14, '2018-06-05 03:15:00', '2018-06-05 03:30:00'),
+(15, '2018-06-05 03:30:00', '2018-06-05 03:45:00'),
+(16, '2018-06-05 03:45:00', '2018-06-05 04:00:00'),
+(17, '2018-06-05 04:00:00', '2018-06-05 04:15:00'),
+(18, '2018-06-05 04:15:00', '2018-06-05 04:30:00'),
+(19, '2018-06-05 04:30:00', '2018-06-05 04:45:00'),
+(20, '2018-06-05 04:45:00', '2018-06-05 05:00:00'),
+(21, '2018-06-05 05:00:00', '2018-06-05 05:15:00'),
+(22, '2018-06-05 05:15:00', '2018-06-05 05:30:00'),
+(23, '2018-06-05 05:30:00', '2018-06-05 05:45:00'),
+(24, '2018-06-05 05:45:00', '2018-06-05 06:00:00'),
+(25, '2018-06-05 06:00:00', '2018-06-05 06:15:00'),
+(26, '2018-06-05 06:15:00', '2018-06-05 06:30:00'),
+(27, '2018-06-05 06:30:00', '2018-06-05 06:45:00'),
+(28, '2018-06-05 06:45:00', '2018-06-05 07:00:00'),
+(29, '2018-06-05 07:00:00', '2018-06-05 07:15:00'),
+(30, '2018-06-05 07:15:00', '2018-06-05 07:30:00'),
+(31, '2018-06-05 07:30:00', '2018-06-05 07:45:00'),
+(32, '2018-06-05 07:45:00', '2018-06-05 08:00:00'),
+(33, '2018-06-05 08:00:00', '2018-06-05 08:15:00'),
+(34, '2018-06-05 08:15:00', '2018-06-05 08:30:00'),
+(35, '2018-06-05 08:30:00', '2018-06-05 08:45:00'),
+(36, '2018-06-05 08:45:00', '2018-06-05 09:00:00'),
+(37, '2018-06-05 09:00:00', '2018-06-05 09:15:00'),
+(38, '2018-06-05 09:15:00', '2018-06-05 09:30:00'),
+(39, '2018-06-05 09:30:00', '2018-06-05 09:45:00'),
+(40, '2018-06-05 09:45:00', '2018-06-05 10:00:00'),
+(41, '2018-06-05 10:00:00', '2018-06-05 10:15:00'),
+(42, '2018-06-05 10:15:00', '2018-06-05 10:30:00'),
+(43, '2018-06-05 10:30:00', '2018-06-05 10:45:00'),
+(44, '2018-06-05 10:45:00', '2018-06-05 11:00:00'),
+(45, '2018-06-05 11:00:00', '2018-06-05 11:15:00'),
+(46, '2018-06-05 11:15:00', '2018-06-05 11:30:00'),
+(47, '2018-06-05 11:30:00', '2018-06-05 11:45:00'),
+(48, '2018-06-05 11:45:00', '2018-06-05 12:00:00'),
+(49, '2018-06-05 12:00:00', '2018-06-05 12:15:00'),
+(50, '2018-06-05 12:15:00', '2018-06-05 12:30:00'),
+(51, '2018-06-05 12:30:00', '2018-06-05 12:45:00'),
+(52, '2018-06-05 12:45:00', '2018-06-05 13:00:00'),
+(53, '2018-06-05 13:00:00', '2018-06-05 13:15:00'),
+(54, '2018-06-05 13:15:00', '2018-06-05 13:30:00'),
+(55, '2018-06-05 13:30:00', '2018-06-05 13:45:00'),
+(56, '2018-06-05 13:45:00', '2018-06-05 14:00:00'),
+(57, '2018-06-05 14:00:00', '2018-06-05 14:15:00'),
+(58, '2018-06-05 14:15:00', '2018-06-05 14:30:00'),
+(59, '2018-06-05 14:30:00', '2018-06-05 14:45:00'),
+(60, '2018-06-05 14:45:00', '2018-06-05 15:00:00'),
+(61, '2018-06-05 15:00:00', '2018-06-05 15:15:00'),
+(62, '2018-06-05 15:15:00', '2018-06-05 15:30:00'),
+(63, '2018-06-05 15:30:00', '2018-06-05 15:45:00'),
+(64, '2018-06-05 15:45:00', '2018-06-05 16:00:00'),
+(65, '2018-06-05 16:00:00', '2018-06-05 16:15:00'),
+(66, '2018-06-05 16:15:00', '2018-06-05 16:30:00'),
+(67, '2018-06-05 16:30:00', '2018-06-05 16:45:00'),
+(68, '2018-06-05 16:45:00', '2018-06-05 17:00:00'),
+(69, '2018-06-05 17:00:00', '2018-06-05 17:15:00'),
+(70, '2018-06-05 17:15:00', '2018-06-05 17:30:00'),
+(71, '2018-06-05 17:30:00', '2018-06-05 17:45:00'),
+(72, '2018-06-05 17:45:00', '2018-06-05 18:00:00'),
+(73, '2018-06-05 18:00:00', '2018-06-05 18:15:00'),
+(74, '2018-06-05 18:15:00', '2018-06-05 18:30:00'),
+(75, '2018-06-05 18:30:00', '2018-06-05 18:45:00'),
+(76, '2018-06-05 18:45:00', '2018-06-05 19:00:00'),
+(77, '2018-06-05 19:00:00', '2018-06-05 19:15:00'),
+(78, '2018-06-05 19:15:00', '2018-06-05 19:30:00'),
+(79, '2018-06-05 19:30:00', '2018-06-05 19:45:00'),
+(80, '2018-06-05 19:45:00', '2018-06-05 20:00:00'),
+(81, '2018-06-05 20:00:00', '2018-06-05 20:15:00'),
+(82, '2018-06-05 20:15:00', '2018-06-05 20:30:00'),
+(83, '2018-06-05 20:30:00', '2018-06-05 20:45:00'),
+(84, '2018-06-05 20:45:00', '2018-06-05 21:00:00'),
+(85, '2018-06-05 21:00:00', '2018-06-05 21:15:00'),
+(86, '2018-06-05 21:15:00', '2018-06-05 21:30:00'),
+(87, '2018-06-05 21:30:00', '2018-06-05 21:45:00'),
+(88, '2018-06-05 21:45:00', '2018-06-05 22:00:00'),
+(89, '2018-06-05 22:00:00', '2018-06-05 22:15:00'),
+(90, '2018-06-05 22:15:00', '2018-06-05 22:30:00'),
+(91, '2018-06-05 22:30:00', '2018-06-05 22:45:00'),
+(92, '2018-06-05 22:45:00', '2018-06-05 23:00:00'),
+(93, '2018-06-05 23:00:00', '2018-06-05 23:15:00'),
+(94, '2018-06-05 23:15:00', '2018-06-05 23:30:00'),
+(95, '2018-06-05 23:30:00', '2018-06-05 23:45:00'),
+(96, '2018-06-05 23:45:00', '2018-06-06 00:00:00'),
+(97, '2018-06-05 00:00:00', '2018-06-06 00:00:00');
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `mesto`
+--
+
+CREATE TABLE IF NOT EXISTS `mesto` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `nazev` varchar(100) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
+
+--
+-- Vypisuji data pro tabulku `mesto`
+--
+
+INSERT INTO `mesto` (`id`, `nazev`) VALUES
+(1, 'Česká Kubice');
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `ulice`
+--
+
+CREATE TABLE IF NOT EXISTS `ulice` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `nazev` varchar(255) NOT NULL,
+  `mesto_id` bigint(20) NOT NULL,
+  `zem_sirka` double NOT NULL DEFAULT '-1' COMMENT 'Zemepisna sirka podle GOOGLE.',
+  `zem_delka` double NOT NULL DEFAULT '-1' COMMENT 'Zemepisna delka podle GOOGLE.',
+  PRIMARY KEY (`id`),
+  KEY `fk_ulice_mesto_idx` (`mesto_id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
+
+--
+-- Vypisuji data pro tabulku `ulice`
+--
+
+INSERT INTO `ulice` (`id`, `nazev`, `mesto_id`, `zem_sirka`, `zem_delka`) VALUES
+(1, 'Česká Kubice', 1, 49.369388, 12.8588044);
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `vozidlo`
+--
+
+CREATE TABLE IF NOT EXISTS `vozidlo` (
+  `id` bigint(20) NOT NULL COMMENT 'Odpovídá číslu skupiny vozidla (TypVozidla10 v csv souboru s daty). Hodnoty 0-10.',
+  `nazev` varchar(45) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Vypisuji data pro tabulku `vozidlo`
+--
+
+INSERT INTO `vozidlo` (`id`, `nazev`) VALUES
+(0, 'Neznámé vozidlo'),
+(1, 'Motocykl'),
+(2, 'Auto'),
+(3, 'Auto s přívěsem'),
+(4, 'Dodávka'),
+(5, 'Dodávka s přívěsem'),
+(6, 'Lehký nákladní automobil'),
+(7, 'Lehký nákladní automobil s přívěsem'),
+(8, 'Nákladní automobil'),
+(9, 'Nákladní automobil s přívěsem'),
+(10, 'Autobus');
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zarizeni`
+--
+
+CREATE TABLE IF NOT EXISTS `zarizeni` (
+  `id` varchar(20) NOT NULL COMMENT 'Odpovídá idDevice v location.csv.',
+  `smer_popis` varchar(255) NOT NULL COMMENT 'Odpovídá Name v locations.csv.',
+  `stav` int(11) NOT NULL,
+  `ulice_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `fk_zarizeni_ulice1_idx` (`ulice_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+--
+-- Vypisuji data pro tabulku `zarizeni`
+--
+
+INSERT INTO `zarizeni` (`id`, `smer_popis`, `stav`, `ulice_id`) VALUES
+('055', 'Česká Kubice, směr od Německa', 0, 1),
+('056', 'Česká Kubice, směr od Babylonu', 0, 1);
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zaznam`
+--
+
+CREATE TABLE IF NOT EXISTS `zaznam` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `vozidla_pocet` int(11) NOT NULL,
+  `rychlost_prumer` double NOT NULL,
+  `vozidla_id` bigint(20) NOT NULL,
+  `zaznam_cas_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `fk_zaznam_vozidla1_idx` (`vozidla_id`),
+  KEY `fk_zaznam_zaznam_cas1_idx` (`zaznam_cas_id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;
+
+--
+-- Vypisuji data pro tabulku `zaznam`
+--
+
+INSERT INTO `zaznam` (`id`, `vozidla_pocet`, `rychlost_prumer`, `vozidla_id`, `zaznam_cas_id`) VALUES
+(1, 1, 12, 0, 1),
+(2, 1, 16, 1, 1),
+(3, 4, 110.49233333333, 2, 1),
+(4, 1, -1, 3, 1),
+(5, 1, 19.05, 4, 1),
+(6, 2, 25.5, 8, 1),
+(7, 1, 65.4, 2, 2),
+(8, 1, 65.789, 6, 2),
+(9, 1, 25.15, 2, 3),
+(10, 1, 12, 0, 4),
+(11, 1, 16, 1, 4),
+(12, 2, 25.5, 2, 4),
+(13, 1, -1, 3, 4),
+(14, 1, 19.05, 4, 4),
+(15, 4, 110.49233333333, 8, 4),
+(16, 1, 25.15, 8, 5);
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zaznam_cas`
+--
+
+CREATE TABLE IF NOT EXISTS `zaznam_cas` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `smer` int(11) NOT NULL COMMENT '1 nebo 2 viz struktura idDetektor v csv souboru s daty.',
+  `zarizeni_id` varchar(20) NOT NULL,
+  `datum_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `fk_zaznam_cas_zarizeni1_idx` (`zarizeni_id`),
+  KEY `datum_id` (`datum_id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
+
+--
+-- Vypisuji data pro tabulku `zaznam_cas`
+--
+
+INSERT INTO `zaznam_cas` (`id`, `smer`, `zarizeni_id`, `datum_id`) VALUES
+(1, 2, '056', 1),
+(2, 1, '056', 2),
+(3, 2, '056', 2),
+(4, 1, '055', 1),
+(5, 1, '055', 2);
+
+-- --------------------------------------------------------
+
+--
+-- Struktura tabulky `zaznam_prum_den`
+--
+
+CREATE TABLE IF NOT EXISTS `zaznam_prum_den` (
+  `id` bigint(20) NOT NULL AUTO_INCREMENT,
+  `vozidla_pocet` int(11) NOT NULL,
+  `rychlost_prumer` double NOT NULL,
+  `smer` int(11) NOT NULL,
+  `zarizeni_id` varchar(20) NOT NULL,
+  `vozidla_id` bigint(20) NOT NULL,
+  `datum_id` bigint(20) NOT NULL,
+  PRIMARY KEY (`id`),
+  KEY `zarizeni_id` (`zarizeni_id`),
+  KEY `vozidla_id` (`vozidla_id`),
+  KEY `datum_id` (`datum_id`)
+) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
+
+--
+-- Vypisuji data pro tabulku `zaznam_prum_den`
+--
+
+INSERT INTO `zaznam_prum_den` (`id`, `vozidla_pocet`, `rychlost_prumer`, `smer`, `zarizeni_id`, `vozidla_id`, `datum_id`) VALUES
+(1, 1, 65.4, 1, '056', 2, 97),
+(2, 1, 65.789, 1, '056', 6, 97),
+(3, 1, 12, 2, '056', 0, 97),
+(4, 1, 16, 2, '056', 1, 97),
+(5, 5, 89.15675, 2, '056', 2, 97),
+(6, 1, -1, 2, '056', 3, 97),
+(7, 1, 19.05, 2, '056', 4, 97),
+(8, 2, 25.5, 2, '056', 8, 97),
+(9, 1, 12, 1, '055', 0, 97),
+(10, 1, 16, 1, '055', 1, 97),
+(11, 2, 25.5, 1, '055', 2, 97),
+(12, 1, -1, 1, '055', 3, 97),
+(13, 1, 19.05, 1, '055', 4, 97),
+(14, 5, 89.15675, 1, '055', 8, 97);
+
+--
+-- Omezení pro exportované tabulky
+--
+
+--
+-- Omezení pro tabulku `ulice`
+--
+ALTER TABLE `ulice`
+  ADD CONSTRAINT `fk_ulice_mesto` FOREIGN KEY (`mesto_id`) REFERENCES `mesto` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zarizeni`
+--
+ALTER TABLE `zarizeni`
+  ADD CONSTRAINT `fk_zarizeni_ulice1` FOREIGN KEY (`ulice_id`) REFERENCES `ulice` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zaznam`
+--
+ALTER TABLE `zaznam`
+  ADD CONSTRAINT `fk_zaznam_vozidla1` FOREIGN KEY (`vozidla_id`) REFERENCES `vozidlo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `fk_zaznam_zaznam_cas1` FOREIGN KEY (`zaznam_cas_id`) REFERENCES `zaznam_cas` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zaznam_cas`
+--
+ALTER TABLE `zaznam_cas`
+  ADD CONSTRAINT `zaznam_cas_ibfk_1` FOREIGN KEY (`datum_id`) REFERENCES `datum` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `fk_zaznam_cas_zarizeni1` FOREIGN KEY (`zarizeni_id`) REFERENCES `zarizeni` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+--
+-- Omezení pro tabulku `zaznam_prum_den`
+--
+ALTER TABLE `zaznam_prum_den`
+  ADD CONSTRAINT `zaznam_prum_den_ibfk_3` FOREIGN KEY (`datum_id`) REFERENCES `datum` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `zaznam_prum_den_ibfk_1` FOREIGN KEY (`zarizeni_id`) REFERENCES `zarizeni` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  ADD CONSTRAINT `zaznam_prum_den_ibfk_2` FOREIGN KEY (`vozidla_id`) REFERENCES `vozidlo` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;