Revize 01f99166

refs #6948: Oprava REST API (/devices, /devices/{id}/time-period) a script pro přidání souřadnic

backend/.env

@@ -6 +6 @@
 DB_HOST=localhost
 DB_PORT=3306
 DB_DATABASE=prujezd_vozidel
-DB_USERNAME=aswi
-DB_PASSWORD=aswi
+DB_USERNAME=root
+DB_PASSWORD=
 
 CACHE_DRIVER=memcached
 QUEUE_DRIVER=sync

backend/app/Http/Controllers/DeviceController.php

@@ -25 +25 @@
     const DIRECTION_PARAM = 'direction';
 
 
-    public function getDevice(Request $request) {
+    public function getDevice(Request $request)
+    {
         $address = null;
-        $showDirection=0;
+        $showDirection = 0;
         if ($request->has(self::ADDRESS_PARAM)) {
             $address = $request->input(self::ADDRESS_PARAM);
         }
@@ -54 +55 @@
      * @param $id
      * @return \Symfony\Component\HttpFoundation\Response
      */
-    public function getDeviceById(Request $request, $id) {
+    public function getDeviceById(Request $request, $id)
+    {
 
         // nacti parametry
         $params = $this->loadDateTimeDirectionConstraints($request);
@@ -66 +68 @@
 
         $device = Zarizeni::findByIdJoinAddress($id);
         if ($device != null) {
-            $device->traffic = Zaznam::findByDevice($id, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction);
+            $device->traffics = Zaznam::findByDevice($id, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction);
             return json_encode($device);
         } else {
             return response('Not found.', 404);
         }
     }
 
-    public function getTrafficAverageByDevice(Request $request, $id) {
+    public function getTrafficAverageByDevice(Request $request, $id)
+    {
         // nacti parametry
         $params = $this->loadDateTimeDirectionConstraints($request);
         $dateFrom = $params[self::DATE_FROM_PARAM];
@@ -84 +87 @@
 
         $device = Zarizeni::findByIdJoinAddress($id);
         if ($device != null) {
-            $device[0]->traffic = Zaznam::averageByDevice($id, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction);
-        } else if ($device == null || count($device) == 0) {
+            $device->dateFrom = $dateFrom;
+            $device->dateTo = $dateTo;
+
+            if ($direction != null) {
+                $device->direction = intval($direction);
+            }
+
+            $device->traffics = Zaznam::averageByDevice($id, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction);
+            return json_encode($device);
+        } else {
             return response('Not found.', 404);
         }
-
-        return $device;
     }
 
-    public function getAll() {
+    public function getAll()
+    {
         return Zarizeni::getAllJoinAddress();
     }
 
-    public function lastDay() {
+    public function lastDay()
+    {
         return Zaznam::lastInsertedDate();
     }
 
-    public function headerTest(Request $request) {
+    public function headerTest(Request $request)
+    {
         $authHeader = $request->header("jwt");
 
-        if($authHeader != null) {
+        if ($authHeader != null) {
             return $authHeader;
         } else {
             return $request->header("jwt");
@@ -116 +128 @@
      *
      * @param Request $request Request ze ktere budou nacitany parametry.
      */
-    private function loadDateTimeDirectionConstraints(Request $request) {
+    private function loadDateTimeDirectionConstraints(Request $request)
+    {
         $params = array();
         $params[self::DATE_FROM_PARAM] = null;
         $params[self::DATE_TO_PARAM] = null;

backend/app/Model/Zarizeni.php

@@ -30 +30 @@
      *
      * @return mixed
      */
-    public static function getAllJoinAddress() {
+    public static function getAllJoinAddress()
+    {
         return DB::table('zarizeni')
             ->join('ulice', 'zarizeni.ulice_id', '=', 'ulice.id')
             ->join('mesto', 'ulice.mesto_id', '=', 'mesto.id')
@@ -52 +53 @@
      * @param $showDirection 1 pokud má být rozlišen směr zařízení.
      * @return mixed
      */
-    public static function findByAddressJoinAddress($address, $showDirection) {
+    public static function findByAddressJoinAddress($address, $showDirection)
+    {
         $query = DB::table('zarizeni')
             ->join('ulice', 'zarizeni.ulice_id', '=', 'ulice.id')
             ->join('mesto', 'ulice.mesto_id', '=', 'mesto.id')
+            ->join('zaznam_cas', 'zaznam_cas.zarizeni_id', '=', 'zarizeni.id')
             ->select('zarizeni.id as id',
                 'zarizeni.smer_popis as name',
                 'ulice.nazev as street',
@@ -63 +66 @@
                 'ulice.lat as lat',
                 'ulice.lng as lng',
                 'mesto.nazev as town',
-                'mesto.id as town_id')
-            ->where('ulice.nazev', 'like', '%'.$address.'%')
-            ->orWhere('mesto.nazev', 'like', '%'.$address.'%');
+                'mesto.id as town_id',
+                $showDirection ? 'zaznam_cas.smer as direction' : DB::Raw('0 as direction'))
+            ->where('ulice.nazev', 'like', '%' . $address . '%')
+            ->orWhere('mesto.nazev', 'like', '%' . $address . '%')
+            ->orWhere('zarizeni.smer_popis', 'like', '%' . $address . '%');
 
         if (!$showDirection) {
-            $query = $query->groupBy('zarizeni.ulice_id');
+            $query = $query->groupBy('zarizeni.id');
+        } else {
+            $query = $query->groupBy('zarizeni.id', 'zaznam_cas.smer');
         }
 
 
@@ -82 +89 @@
      * @param $id Id zarizeni.
      * @return mixed
      */
-    public static function findByIdJoinAddress($id) {
+    public static function findByIdJoinAddress($id)
+    {
         return DB::table('zarizeni')
             ->join('ulice', 'zarizeni.ulice_id', '=', 'ulice.id')
             ->join('mesto', 'ulice.mesto_id', '=', 'mesto.id')
@@ -120 +128 @@
      */
     public $stav;
 
-    public function ulice() {
+    public function ulice()
+    {
         return $this->belongsTo('App\Model\Ulice');
     }
 }

backend/app/Model/Zaznam.php

@@ -23 +23 @@
      * Vrati posledni datum pro ktere existuji nejake zaznamy.
      * @return String Posledni datum pro ktere existuji zaznamy.
      */
-    public static function lastInsertedDate() {
+    public static function lastInsertedDate()
+    {
         return DB::table('zaznam_cas')->select(DB::raw('max(date(datetime_od)) as last_day'))->get();
     }
 
@@ -38 +39 @@
      * @param int $direction Pozadovany smer. Null znamena oba smery.
      * @return array Prumery dopravy pro casovy usek podle typu vozidla.
      */
-    public static function averageByDevice($deviceId, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction) {
-        $dateTimeFrom = null;
-        $dateTimeTo = null;
+    public static function averageByDevice($deviceId, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction)
+    {
         $lastDate = null;
         $dir = null;
 
@@ -54 +54 @@
                 $lastDate = $lastDate[0]->last_day;
             }
         }
-        $dateTimeFrom = self::concatDateTime($dateFrom, $timeFrom, $lastDate, '00:00:00');
-        $dateTimeTo = self::concatDateTime($dateTo, $timeTo, $lastDate, '23:59:59');
-
 
         // vytvoreni query - vsechno to dat dohromady
-        $query = DB::table('zaznam')
-            ->join('zaznam_cas', 'zaznam.zaznam_cas_id', '=', 'zaznam_cas.id')
-            ->join('vozidlo', 'zaznam.vozidlo_id', '=', 'vozidlo.id')
+        $query = DB::table('zaznam_cas')
+            ->join('zaznam', 'zaznam.zaznam_cas_id', '=', 'zaznam_cas.id')
             ->select(DB::raw("
-                date_format(zaznam_cas.datetime_od, '%Y-%m-%d') as dateFrom,
-                date_format(zaznam_cas.datetime_do,  '%Y-%m-%d') as dateTo,
-                date_format(zaznam_cas.datetime_od, '%H:%i:%s') as timeFrom,
-                date_format(zaznam_cas.datetime_do, '%H:%i:%s') as timeTo,
-                zaznam_cas.smer as direction,
-                avg(zaznam.rychlost_prumer) as speedAverage,
-                sum(zaznam.vozidla_pocet) as numberVehicle,
-                vozidlo.nazev as typeVehicle,
-                vozidlo.id as typeVehicleId
+                date_format(zaznam_cas.datetime_od, '%H:%i') as timeFrom,
+                date_format(zaznam_cas.datetime_do, '%H:%i') as timeTo,
+                ROUND(avg(zaznam.rychlost_prumer),0) as speedAverage,
+                CAST(sum(zaznam.vozidla_pocet) as UNSIGNED) as numberVehicle,
+                ROUND(avg(zaznam.vozidla_pocet),0) as numberVehicleAverage,
+                zaznam.vozidlo_id as typeVehicleId
             "))
-            ->where('zaznam_cas.datetime_od', '>=', $dateTimeFrom)
-            ->where('zaznam_cas.datetime_do', '<=', $dateTimeTo)
-            ->where('zaznam_cas.zarizeni_id', '=', $deviceId)
-        ;
+            ->whereDate('zaznam_cas.datetime_od', '>=', $dateFrom == null ? $lastDate : $dateFrom)
+            ->whereDate('zaznam_cas.datetime_do', '<=', $dateTo == null ? $lastDate : $dateTo)
+            ->whereTime('zaznam_cas.datetime_od', '>=', $timeFrom == null ? '08:00:00' : $timeFrom)
+            ->whereTime('zaznam_cas.datetime_od', '<=', $timeTo == null ? '23:59:59' : $timeTo)
+            ->where('zaznam_cas.zarizeni_id', '=', $deviceId);
 
-        if($direction != null) {
+        if ($direction != null) {
             $query = $query->where('zaznam_cas.smer', '=', $direction);
         }
 
         // pridat grouping a razeni nakonec
         $query = $query
-                ->groupBy('timeFrom', 'timeTo', 'typeVehicleId')
-                ->orderBy('dateFrom', 'asc')
-                ->orderBy('timeFrom', 'asc')
-                ->orderBy('typeVehicleId', 'asc');
+            ->groupBy('zaznam_cas.datetime_od', 'zaznam.vozidlo_id')
+            ->orderBy('zaznam_cas.datetime_od', 'asc')
+            ->orderBy('zaznam.vozidlo_id', 'asc');
 
         return $query->get();
     }
@@ -104 +97 @@
      * @param int $direction Pozadovany smer. Null znamena oba smery.
      * @return array Zaznamy o doprave v casovem useku pro dane zarizeni.
      */
-    public static function findByDevice($deviceId, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction) {
+    public static function findByDevice($deviceId, $dateFrom, $dateTo, $timeFrom, $timeTo, $direction)
+    {
         $dateTimeFrom = null;
         $dateTimeTo = null;
         $lastDate = null;
@@ -139 +133 @@
             ->where('zaznam_cas.datetime_do', '<=', $dateTimeTo)
             ->where('zaznam_cas.zarizeni_id', '=', $deviceId);
 
-        if($direction != null) {
+        if ($direction != null) {
             $query = $query->where('zaznam_cas.smer', '=', $direction);
         }
 
@@ -160 +154 @@
      * @param String $defTime Defaultni hodnota pro casovou slozku.
      * @return String Spojene datum a cas.
      */
-    private static function concatDateTime($date, $time, $defDate, $defTime) {
+    private static function concatDateTime($date, $time, $defDate, $defTime)
+    {
         $dateTime = null;
         $d = $date == null ? $defDate : $date;
         $t = $time == null ? $defTime : $time;
@@ -168 +163 @@
         $dateTime = date('Y-m-d H:i:s', strtotime("$d $t"));
         return $dateTime;
     }
+
 }

db/v5__geosouradnice_update.sql

@@ +1 @@
+
+UPDATE `ulice` SET lat=49.369388,lng=12.858804400000054 WHERE id=1; 
+UPDATE `ulice` SET lat=49.5942514,lng=13.100573599999962 WHERE id=2;
+UPDATE `ulice` SET lat=49.5325795,lng=12.932922299999973 WHERE id=3;
+UPDATE `ulice` SET lat=49.5331401,lng=12.944697200000064 WHERE id=4;
+UPDATE `ulice` SET lat=49.5290441,lng=12.924181900000008 WHERE id=5;
+UPDATE `ulice` SET lat=49.4348481,lng=12.814734700000031 WHERE id=6;
+UPDATE `ulice` SET lat=49.3661949,lng=13.134370499999932 WHERE id=7;
+UPDATE `ulice` SET lat=49.5073853,lng=12.801840500000026 WHERE id=8;
+UPDATE `ulice` SET lat=49.5511106,lng=13.065398100000039 WHERE id=9;
+UPDATE `ulice` SET lat=49.3016091,lng=13.312576700000022 WHERE id=10;
+UPDATE `ulice` SET lat=49.5018924,lng=13.294453100000055 WHERE id=11;
+UPDATE `ulice` SET lat=49.1962834,lng=13.509260100000006 WHERE id=12;
+UPDATE `ulice` SET lat=49.2989568,lng=13.439006199999994 WHERE id=13;
+UPDATE `ulice` SET lat=49.726864,lng=12.901699000000008 WHERE id=14;
+UPDATE `ulice` SET lat=49.8893779,lng=12.606704600000057 WHERE id=15;
+UPDATE `ulice` SET lat=49.8067796,lng=12.642488400000047 WHERE id=16;
+UPDATE `ulice` SET lat=49.7911169,lng=12.648667199999977 WHERE id=17;
+UPDATE `ulice` SET lat=49.8030504,lng=12.63480770000001 WHERE id=18;
+UPDATE `ulice` SET lat=49.4329948,lng=12.951485300000058 WHERE id=19;
+UPDATE `ulice` SET lat=49.5615961,lng=12.77715139999998 WHERE id=20;
+UPDATE `ulice` SET lat=49.5891548,lng=12.881511600000067 WHERE id=21;
+UPDATE `ulice` SET lat=49.4365627,lng=12.720542000000023 WHERE id=22;
+UPDATE `ulice` SET lat=49.3667667,lng=13.009845799999994 WHERE id=23;
+UPDATE `ulice` SET lat=49.8416809,lng=13.229441100000031 WHERE id=24;
+UPDATE `ulice` SET lat=49.34425969999999,lng=13.219951100000003 WHERE id=25;
+UPDATE `ulice` SET lat=49.3590594,lng=13.384042300000033 WHERE id=26;
+UPDATE `ulice` SET lat=49.3603773,lng=13.356904600000007 WHERE id=27;
+UPDATE `ulice` SET lat=49.3909746,lng=13.468732700000032 WHERE id=28;
+UPDATE `ulice` SET lat=49.6655193,lng=12.827393700000016 WHERE id=29;
+UPDATE `ulice` SET lat=49.591775,lng=12.705252900000005 WHERE id=30;
+UPDATE `ulice` SET lat=49.5893638,lng=12.720079899999973 WHERE id=31;
+UPDATE `ulice` SET lat=49.3247489,lng=13.69061750000003 WHERE id=32;
+UPDATE `ulice` SET lat=49.3195422,lng=13.71081079999999 WHERE id=33;
+UPDATE `ulice` SET lat=49.323759,lng=13.703250000000025 WHERE id=34;
+UPDATE `ulice` SET lat=49.1460905,lng=13.564452399999936 WHERE id=35;
+UPDATE `ulice` SET lat=49.0240725,lng=13.499317499999961 WHERE id=36;
+UPDATE `ulice` SET lat=49.0285263,lng=13.517740499999945 WHERE id=37;
+UPDATE `ulice` SET lat=49.2881779,lng=13.143561800000043 WHERE id=38;
+UPDATE `ulice` SET lat=49.2863376,lng=13.12969309999994 WHERE id=39;
+UPDATE `ulice` SET lat=49.293581,lng=13.125704600000063 WHERE id=40;
+UPDATE `ulice` SET lat=49.2951762,lng=13.150141200000007 WHERE id=41;
+UPDATE `ulice` SET lat=49.2276062,lng=13.520478400000002 WHERE id=42;
+UPDATE `ulice` SET lat=49.2234632,lng=13.511268900000005 WHERE id=43;
+UPDATE `ulice` SET lat=49.24249409999999,lng=13.532533599999965 WHERE id=44;
+UPDATE `ulice` SET lat=49.1374427,lng=13.235204599999975 WHERE id=45;
+UPDATE `ulice` SET lat=49.7173149,lng=12.777892800000018 WHERE id=46;
+UPDATE `ulice` SET lat=49.893186,lng=12.730072699999937 WHERE id=47;
+UPDATE `ulice` SET lat=49.863975,lng=12.725196099999948 WHERE id=48;
+UPDATE `ulice` SET lat=49.6681792,lng=12.551898199999982 WHERE id=49;
+UPDATE `ulice` SET lat=49.752874,lng=12.989615299999969 WHERE id=50;
+UPDATE `ulice` SET lat=49.7515438,lng=13.00579289999996 WHERE id=51;
+UPDATE `ulice` SET lat=49.3986612,lng=12.862793099999976 WHERE id=52;
+UPDATE `ulice` SET lat=49.3825977,lng=13.049589999999966 WHERE id=53;
+UPDATE `ulice` SET lat=49.59149559999999,lng=12.715902400000004 WHERE id=54;
+UPDATE `ulice` SET lat=49.4462758,lng=12.932268199999953 WHERE id=55;
+UPDATE `ulice` SET lat=49.4417745,lng=12.917492100000004 WHERE id=56;
+UPDATE `ulice` SET lat=49.5316687,lng=12.951474599999983 WHERE id=57;
+UPDATE `ulice` SET lat=49.5256309,lng=12.93846629999996 WHERE id=58;
+UPDATE `ulice` SET lat=49.4188422,lng=12.716298300000062 WHERE id=59;
+UPDATE `ulice` SET lat=49.3434229,lng=13.133497300000045 WHERE id=60;
+UPDATE `ulice` SET lat=49.4249957,lng=13.285349600000018 WHERE id=61;
+UPDATE `ulice` SET lat=49.3858178,lng=13.304274299999975 WHERE id=62;
+UPDATE `ulice` SET lat=49.3831621,lng=13.297191399999974 WHERE id=63;
+UPDATE `ulice` SET lat=49.4024071,lng=13.293027400000028 WHERE id=64;
+UPDATE `ulice` SET lat=49.3939022,lng=13.28203080000003 WHERE id=65;
+UPDATE `ulice` SET lat=49.402985,lng=13.28112520000002 WHERE id=66;
+UPDATE `ulice` SET lat=49.3954174,lng=13.304848499999935 WHERE id=67;
+UPDATE `ulice` SET lat=49.7114696,lng=12.772997000000032 WHERE id=68;
+UPDATE `ulice` SET lat=49.7088027,lng=12.782785699999977 WHERE id=69;
+UPDATE `ulice` SET lat=49.6779674,lng=12.654376100000036 WHERE id=70;
+UPDATE `ulice` SET lat=49.0871919,lng=13.480447700000013 WHERE id=71;
+UPDATE `ulice` SET lat=49.8255089,lng=12.523725000000013 WHERE id=72;
+UPDATE `ulice` SET lat=49.348875,lng=12.85527849999994 WHERE id=73;
+UPDATE `ulice` SET lat=49.2316758,lng=13.520409299999983 WHERE id=74;
+UPDATE `ulice` SET lat=49.6692003,lng=13.448342300000036 WHERE id=75;
+UPDATE `ulice` SET lat=49.696732,lng=13.459893899999997 WHERE id=76;
+UPDATE `ulice` SET lat=49.62768089999999,lng=13.152081500000008 WHERE id=77;
+UPDATE `ulice` SET lat=49.4861699,lng=13.582260899999937 WHERE id=78;
+UPDATE `ulice` SET lat=49.6404353,lng=13.519626600000038 WHERE id=79;
+UPDATE `ulice` SET lat=49.89313869999999,lng=13.382958300000041 WHERE id=80;
+UPDATE `ulice` SET lat=49.981924,lng=13.487483500000053 WHERE id=81;
+UPDATE `ulice` SET lat=49.76380169999999,lng=13.252113799999961 WHERE id=82;
+UPDATE `ulice` SET lat=49.6784086,lng=13.22565439999994 WHERE id=83;
+UPDATE `ulice` SET lat=49.6548327,lng=13.29307730000005 WHERE id=84;
+UPDATE `ulice` SET lat=49.9344975,lng=13.390364999999974 WHERE id=85;
+UPDATE `ulice` SET lat=49.7298911,lng=13.276285899999948 WHERE id=86;
+UPDATE `ulice` SET lat=49.5729906,lng=13.33350710000002 WHERE id=87;