Revize 4877913b

refs #6844: Pridana implementace api endpointu pro ziskavani prumeru dopravy v casovem useku podle zarizeni.

backend/app/Http/Controllers/DeviceController.php

@@ -21 +21 @@
     const DATE_FROM_PARAM = 'dateFrom';
     const DATE_TO_PARAM = 'dateTo';
     const TIME_FROM_PARAM = 'timeFrom';
-    const TIME_FROM_TO = 'timeTo';
+    const TIME_TO_PARAM = 'timeTo';
     const DIRECTION_PARAM = 'direction';
 
 
     public function getDevice(Request $request) {
         $address = null;
         $showDirection=0;
-        if ($request->has(ADDRESS_PARAM)) {
-            $address = $request->input(ADDRESS_PARAM);
+        if ($request->has(self::ADDRESS_PARAM)) {
+            $address = $request->input(self::ADDRESS_PARAM);
         }
 
-        if ($request->has(SHOW_DIRECTION_PARAM) && $request->input(SHOW_DIRECTION_PARAM) == '1') {
+        if ($request->has(self::SHOW_DIRECTION_PARAM) && $request->input(self::SHOW_DIRECTION_PARAM) == '1') {
             $showDirection = 1;
         }
 
@@ -59 +59 @@
      */
     public function getDeviceById(Request $request, $id) {
 
-        $dateFrom = null;
-        $dateTo = null;
-        $timeFrom = null;
-        $timeTo = null;
-        $direction = null;
-
         // nacti parametry
-        if ($request->has(DATE_FROM_PARAM)) {
-            $dateFrom = $request->input(DATE_FROM_PARAM);
-        }
-        if ($request->has(DATE_TO_PARAM)) {
-            $dateTo = $request->input(DATE_TO_PARAM);
-        }
-        if ($request->has(TIME_FROM_PARAM)) {
-            $timeFrom = $request->input(TIME_FROM_PARAM);
-        }
-        if ($request->has(TIME_TO_PARAM)) {
-            $timeTo = $request->input(TIME_TO_PARAM);
-        }
-        if ($request->has(DIRECTION_PARAM)) {
-            $direction = $request->input(DIRECTION_PARAM);
-        }
+        $params = $this->loadDateTimeDirectionConstraints($request);
+        $dateFrom = $params[self::DATE_FROM_PARAM];
+        $dateTo = $params[self::DATE_TO_PARAM];
+        $timeFrom = $params[self::TIME_FROM_PARAM];
+        $timeTo = $params[self::TIME_TO_PARAM];
+        $direction = $params[self::DIRECTION_PARAM];
 
         $device = Zarizeni::findByIdJoinAddress($id);
         if ($device != null) {
@@ -92 +77 @@
         return $device;
     }
 
+    public function getTrafficAverageByDevice(Request $request, $id) {
+        // nacti parametry
+        $params = $this->loadDateTimeDirectionConstraints($request);
+        $dateFrom = $params[self::DATE_FROM_PARAM];
+        $dateTo = $params[self::DATE_TO_PARAM];
+        $timeFrom = $params[self::TIME_FROM_PARAM];
+        $timeTo = $params[self::TIME_TO_PARAM];
+        $direction = $params[self::DIRECTION_PARAM];
+
+        $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) {
+            return response('Not found.', 404);
+        }
+
+        return $device;
+    }
+
     public function getAll() {
         return Zarizeni::getAllJoinAddress();
     }
@@ -109 +113 @@
             return $request->header("jwt");
         }
     }
+
+    /**
+     * Funkce nacte z requestu url parametry dateFrom, dateTo, timeFrom, timeTo, direciton a vrati je jako pole.
+     * Defaultni hodnoty jsou null.
+     *
+     * @param Request $request Request ze ktere budou nacitany parametry.
+     */
+    private function loadDateTimeDirectionConstraints(Request $request) {
+        $params = array();
+        $params[self::DATE_FROM_PARAM] = null;
+        $params[self::DATE_TO_PARAM] = null;
+        $params[self::TIME_FROM_PARAM] = null;
+        $params[self::TIME_TO_PARAM] = null;
+        $params[self::DIRECTION_PARAM] = null;
+
+        if ($request->has(self::DATE_FROM_PARAM)) {
+            $params[self::DATE_FROM_PARAM] = $request->input(self::DATE_FROM_PARAM);
+        }
+        if ($request->has(self::DATE_TO_PARAM)) {
+            $params[self::DATE_TO_PARAM] = $request->input(self::DATE_TO_PARAM);
+        }
+        if ($request->has(self::TIME_FROM_PARAM)) {
+            $params[self::TIME_FROM_PARAM] = $request->input(self::TIME_FROM_PARAM);
+        }
+        if ($request->has(self::TIME_TO_PARAM)) {
+            $params[self::TIME_TO_PARAM] = $request->input(self::TIME_TO_PARAM);
+        }
+        if ($request->has(self::DIRECTION_PARAM)) {
+            $params[self::DIRECTION_PARAM] = $request->input(self::DIRECTION_PARAM);
+        }
+
+        return $params;
+    }
 }

backend/app/Http/routes.php

@@ -21 +21 @@
 });
 
 /**
- * Parametry v url:
- * address
- * showDirection
+ * Vrati seznam mericich zarizeni.
  */
 $app->get($apiUrlRoot.'devices', [
     'middleware' => 'jwtauth',
@@ -32 +30 @@
 
 
 /**
- * Parametry v url:
- * dateFrom
- * dateTo
- * timeFrom
- * timeTo
- * direction
+ * Vrati zaznamy o doprav e za casovy usek pro dane zarizeni.
  */
 $app->get($apiUrlRoot.'devices/{id}', [
     'middleware' => 'jwtauth',
     'uses' => 'DeviceController@getDeviceById'
 ]);
 
+/**
+ * Vrati prumery dopravy pro danze zarizeni za casovy usek.
+ */
+$app->get($apiUrlRoot.'devices/{id}/time-period', [
+   'middleware' => 'jwtauth',
+    'uses' => 'DeviceController@getTrafficAverageByDevice'
+]);
+
 
 /**
  * Vrati vsechny typy aut.

backend/app/Model/Zarizeni.php

@@ -82 +82 @@
         return DB::table('zarizeni')
             ->join('ulice', 'zarizeni.ulice_id', '=', 'ulice.id')
             ->join('mesto', 'ulice.mesto_id', '=', 'mesto.id')
-            ->select('zarizeni.id as id', 'zarizeni.smer_popis as name', 'ulice.nazev as street', 'ulice.id as street_id', 'mesto.nazev as town', 'mesto.id as town_id')
+            ->select('zarizeni.id as id',
+                'zarizeni.smer_popis as name',
+                'ulice.nazev as street',
+                'ulice.id as street_id',
+                'mesto.nazev as town',
+                'mesto.id as town_id')
             ->where('zarizeni.id', '=', $id)
             ->orderBy('zarizeni.id')
             ->get();

backend/app/Model/Zaznam.php

@@ -21 +21 @@
 
     /**
      * Vrati posledni datum pro ktere existuji nejake zaznamy.
+     * @return String Posledni datum pro ktere existuji zaznamy.
      */
     public static function lastInsertedDate() {
         return DB::table('zaznam_cas')->select(DB::raw('max(date(datetime_od)) as last_day'))->get();
     }
 
+    /**
+     * Vrati prumery dopravy v casovem useku podle typu vozidla.
+     *
+     * @param String $deviceId Id zarizeni pro ktere budou vraceny zaznamy.
+     * @param String $dateFrom Pocatecni datum. Null znamená poledni vlozeny den.
+     * @param String $dateTo Koncove datum. Null znamena posledni vlozeny den.
+     * @param String $timeFrom Pocatecni cas. Null znamena 00:00.
+     * @param String $timeTo Koncovy cas. Null znamena 23:59.
+     * @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;
+        $lastDate = null;
+        $dir = null;
+
+        // jedno z omezujicich dat je null => ziskej posledni vlozene datum
+        if ($dateFrom == null || $dateTo == null) {
+            $lastDate = Zaznam::lastInsertedDate();
+            if ($lastDate == null) {
+                // database is empty
+                return "no-data";
+            } else {
+                $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')
+            ->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
+            "))
+            ->where('zaznam_cas.datetime_od', '>=', $dateTimeFrom)
+            ->where('zaznam_cas.datetime_do', '<=', $dateTimeTo)
+            ->where('zaznam_cas.zarizeni_id', '=', $deviceId)
+        ;
+
+        if($direction != null) {
+            $query = $query->where('zaznam_cas.smer', '=', $direction);
+        }
+
+        // pridat grouping a razeni nakonec
+        $query = $query
+                ->groupBy('timeFrom', 'timeTo', 'typeVehicleId')
+                ->orderBy('dateFrom','timeFrom', 'typeVehicleId');
+
+        return $query->get();
+    }
+
     /**
      * Vrati zaznamy pro urcite zarizeni.
      * Typ vozidla je vracen s kazdym zaznamem.
      *
-     * @param $deviceId Id zarizeni pro ktere budou vraceny zaznamy.
-     * @param $dateFrom Pocatecni datum. Null znamená poledni vlozeny den.
-     * @param $dateTo Koncove datum. Null znamena posledni vlozeny den.
-     * @param $timeFrom Pocatecni cas. Null znamena 00:00.
-     * @param $timeTo Koncovy cas. Null znamena 23:59.
-     * @param $direction Pozadovany smer. Null znamena oba smery.
+     * @param String $deviceId Id zarizeni pro ktere budou vraceny zaznamy.
+     * @param String $dateFrom Pocatecni datum. Null znamená poledni vlozeny den.
+     * @param String $dateTo Koncove datum. Null znamena posledni vlozeny den.
+     * @param String $timeFrom Pocatecni cas. Null znamena 00:00.
+     * @param String $timeTo Koncovy cas. Null znamena 23:59.
+     * @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) {
         $dateTimeFrom = null;
@@ -48 +113 @@
             $lastDate = Zaznam::lastInsertedDate();
             if ($lastDate == null) {
                 // database is empty
-                // todo: error codes
                 return "no-data";
             } else {
                 $lastDate = $lastDate[0]->last_day;
             }
         }
-        if ($dateFrom == null) {
-            $dateFrom = $lastDate;
-        }
-        if ($dateTo == null) {
-            $dateTo = $lastDate;
-        }
-        // omezujici casy
-        if ($timeFrom == null) {
-            $timeFrom = '00:00:00';
-        }
-        if ($timeTo == null) {
-            $timeTo = '23:59:59';
-        }
-        $dateTimeFrom = date('Y-m-d H:i:s', strtotime("$dateFrom $timeFrom"));
-        $dateTimeTo = date('Y-m-d H:i:s', strtotime("$dateTo $timeTo"));
+        $dateTimeFrom = self::concatDateTime($dateFrom, $timeFrom, $lastDate, '00:00:00');
+        $dateTimeTo = self::concatDateTime($dateTo, $timeTo, $lastDate, '23:59:59');
 
 
         // vytvoreni query - vsechno to dat dohromady
@@ -92 +143 @@
 
         return $query->get();
     }
+
+    /**
+     * Spoji datum a cas. Pokud je jedna ze slozek null,
+     * pouzije se defaultni hodnota.
+     *
+     * Vysledny datovy format: Y-m-d H:i:s
+     *
+     * @param String $date Datova slozka.
+     * @param String $time Casova slozka.
+     * @param String $defDate Defaultni hodnota pro datovou slozku.
+     * @param String $defTime Defaultni hodnota pro casovou slozku.
+     * @return String Spojene datum a cas.
+     */
+    private static function concatDateTime($date, $time, $defDate, $defTime) {
+        $dateTime = null;
+        $d = $date == null ? $defDate : $date;
+        $t = $time == null ? $defTime : $time;
+
+        $dateTime = date('Y-m-d H:i:s', strtotime("$d $t"));
+        return $dateTime;
+    }
 }