From bd71ab52274e6864441c8f435301010b4d9928bc Mon Sep 17 00:00:00 2001
From: CMDR furrycat <elite@furrycat.net>
Date: Mon, 1 Feb 2016 05:20:32 -0500
Subject: [PATCH] More entry graphs.

---
 lib/race.php | 58 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
 race.js      | 16 +++++++++++++++-
 2 files changed, 72 insertions(+), 2 deletions(-)

diff --git a/lib/race.php b/lib/race.php
index f09895a..bca7fb5 100644
--- a/lib/race.php
+++ b/lib/race.php
@@ -462,8 +462,61 @@
           $plots["lap_distance"]["labels"][] = $lap_distance;
         }
         $plots["lap_distance"]["pull"] = pull($plots["lap_distance"]["values"]);
+
+        $legs = array(
+          "leg_times" => array(
+            "x" => array(),
+            "y" => array(),
+            "name" => "Leg times",
+            "type" => "bar"
+          )
+        );
+        $speeds = array(
+          "leg_speed" => array(
+            "x" => array(),
+            "y" => array(),
+            "name" => "Leg speed",
+            "type" => "bar"
+          )
+        );
+        $laps = array(
+          "fastest_laps" => array(
+            "x" => array(),
+            "y" => array(),
+            "name" => "Fastest race lap",
+            "type" => "bar"
+          )
+        );
+        $dbh = Propel::getConnection();
+        /* Average station to station speed. */
+        $sth = $dbh->prepare("select s1.name as station1, s2.name as station2, avg(timestampdiff(second, l1.arrival, l2.arrival)) as delta from laptime l1, laptime l2, station s1, station s2, lap, entry, course where l1.lap_id=l2.lap_id and l1.station_id=s1.id and l2.station_id=s2.id and l1.lap_id=lap.id and lap.entry_id=entry.id and course.race_id=entry.race_id and l2.station_order=l1.station_order+1 and entry.race_id=:race_id group by station1, station2 order by delta");
+        $sth->execute(array(":race_id" => $id));
+        while ($row = $sth->fetch()) {
+          $legs["leg_times"]["x"][] = sprintf("%s - %s", $row["station1"], $row["station2"]);
+          $legs["leg_times"]["y"][] = (int) $row["delta"];
+        }
+        /* Average station to station speed. */
+        $sth = $dbh->prepare("select s1.name as station1, s2.name as station2, avg((distance / (timestampdiff(second, l1.arrival, l2.arrival) / 3600))) as speed from laptime l1, laptime l2, station s1, station s2, lap, entry, course, raikogram where l1.lap_id=l2.lap_id and l1.station_id=s1.id and l2.station_id=s2.id and l1.lap_id=lap.id and lap.entry_id=entry.id and system1=(select case when s1.system_id<s2.system_id then s1.system_id else s2.system_id end) and system2=(select case when s1.system_id<s2.system_id then s2.system_id else s1.system_id end) and course.race_id=entry.race_id and l2.station_order=l1.station_order+1 and entry.race_id=:race_id group by station1, station2 order by speed desc");
+        $sth->execute(array(":race_id" => $id));
+        while ($row = $sth->fetch()) {
+          $speeds["leg_speed"]["x"][] = sprintf("%s - %s", $row["station1"], $row["station2"]);
+          $speeds["leg_speed"]["y"][] = (float) $row["speed"];
+        }
+        /* Average time per lap. */
+        //$sth = $dbh->prepare("select lap_number, avg(delta) from (select lap_number, timestampdiff(second, min(arrival), max(arrival)) as delta from laptime, lap, entry where laptime.lap_id=lap.id and lap.entry_id=entry.id and lap_number<=entry.complete_laps and race_id=:race_id group by entry_id, lap_number) as aggregate_laps group by lap_number");
+        //$sth->execute(array(":race_id" => $id));
+        /* Fastest individual lap. */
+        $sth = $dbh->prepare("select lap_number, count(*) as n from (select lap_number, delta from (select entry_id, lap_number, timestampdiff(second, min(arrival), max(arrival)) as delta from laptime, lap, entry where laptime.lap_id=lap.id and lap.entry_id=entry.id and lap_number<=entry.complete_laps and race_id=:race_id group by entry_id, lap_number order by entry_id, delta) as aggregate_laps group by entry_id) as aggregate_fastest group by lap_number");
+        $sth->execute(array(":race_id" => $id));
+        while ($row = $sth->fetch()) {
+          $laps["fastest_laps"]["x"][] = "Lap " . $row["lap_number"];
+          $laps["fastest_laps"]["y"][] = (int) $row["n"];
+        }
         echo "<script type=\"text/javascript\"><!--\n";
         echo "var pies = " . json_encode(array_values($plots)) . ";\n";
+        echo "var legs = " . json_encode(array_values($legs)) . ";\n";
+        echo "var speeds = " . json_encode(array_values($speeds)) . ";\n";
+        echo "var laps = " . json_encode(array_values($laps)) . ";\n";
         echo "//--></script>\n";
       }
       else {
@@ -472,7 +525,10 @@
       }
 
       echo "<h3>$name</h3>\n";
-      echo "<div id=\"plot\"></div>\n";
+      echo "<div id=\"plot_distribution\"></div>\n";
+      echo "<div id=\"plot_legs\"></div>\n";
+      echo "<div id=\"plot_speeds\"></div>\n";
+      echo "<div id=\"plot_laps\"></div>\n";
       echo "<div class=\"classification\">\n";
       foreach (array('html', 'bbcode') as $type) {
         echo "<div class=\"$type\">\n";
diff --git a/race.js b/race.js
index 099f757..7c09805 100644
--- a/race.js
+++ b/race.js
@@ -150,10 +150,24 @@ function loaded() {
     }
     validate();
   }
-  var canvas = document.getElementById('plot');
+  var canvas = document.getElementById('plot_distribution');
   if (canvas && pies) Plotly.plot(canvas, pies, {
     title: 'Entry distribution'
   });
+  canvas = document.getElementById('plot_legs');
+  if (canvas && legs) Plotly.plot(canvas, legs, {
+    title: 'Average station to station time',
+    yaxis: { title: 'Time (s)' }
+  });
+  canvas = document.getElementById('plot_speeds');
+  if (canvas && speeds) Plotly.plot(canvas, speeds, {
+    title: 'Average station to station speed',
+    yaxis: { title: 'Speed (Ly/h)' }
+  });
+  canvas = document.getElementById('plot_laps');
+  if (canvas && laps) Plotly.plot(canvas, laps, {
+    title: 'Fastest individual laps',
+  });
 }
 
 document.onload = loaded();
-- 
2.7.4