Revize 01f99166

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

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;
     }
+
 }