NEW FEATURE + CLEANUP: added some table sorting to the statistics page via jquery
[e-DoKo.git] / include / stats.php
index 83c11ad1ce1f091f25fe548bdb3c5212f554b453..1e6a033ea5986fca7bd4a11610cdb7b2118aef33 100644 (file)
@@ -38,9 +38,7 @@ if( !$content = getCache("cache/stats.html",60*60*24) )
 
   echo "<p>The contra party wins in ";
   $result = DB_query("SELECT COUNT(*) from Score".
-                    " LEFT JOIN Game ON Game.id=game_id".
-                    " WHERE score='againstqueens'".
-                    " AND Game.status='gameover'");
+                    " WHERE score='againstqueens'");
   while( $r = DB_fetch_array($result))
     echo $r[0];
   echo " games.</p>\n";
@@ -173,15 +171,6 @@ if( !$content = getCache("cache/stats.html",60*60*24) )
   array_unshift($result,array("Position","Number of tricks"));
   echo output_table($result,"Tricks at the table","stats");
 
-  /* most games */
-  $result = DB_query_array_all("SELECT fullname, COUNT(*) as c  " .
-                              " FROM Hand".
-                              " LEFT JOIN User ON User.id=user_id".
-                              " GROUP BY user_id".
-                              " ORDER BY c DESC LIMIT 7" );
-  array_unshift($result,array("Name","Number of games"));
-  echo output_table($result,"Most games","stats");
-
   /* most solos */
   $result = DB_query_array_all("SELECT fullname as fname,".
                               "       COUNT(*), ".
@@ -207,6 +196,21 @@ if( !$content = getCache("cache/stats.html",60*60*24) )
   array_unshift($result,array("Name","Number of active games"));
   echo output_table($result,"Active games","stats");
 
+  /* response time of users*/
+  $result = DB_query_array_all("SELECT User.fullname,".
+                              "IFNULL(AVG(if(P1.sequence in (2,3,4),".
+                              "-timestampdiff(MINUTE,mod_date,(select mod_date from Play P2 where P1.trick_id=P2.trick_id  and P2.sequence=P1.sequence-1)),NULL )),1e9) as a, ".
+                              " COUNT(*) as na ".
+                              "FROM Play P1 ".
+                              "LEFT JOIN Hand_Card ON P1.hand_card_id=Hand_Card.id ".
+                              "LEFT JOIN Hand ON Hand.id=Hand_Card.hand_id ".
+                              "LEFT JOIN User ON Hand.user_id=User.id ".
+                              "GROUP BY user_id ".
+                              "HAVING na>8 ".
+                              "ORDER BY a " );
+  array_unshift($result,array("Name","Average minutes before response","trick count"));
+  echo output_table($result,"Response","stats");
+
   /*
  does the party win more often if they start
 
@@ -221,12 +225,12 @@ if( !$content = getCache("cache/stats.html",60*60*24) )
  echo " games</p>\n";
   */
   $result = generate_global_score_table();
-  array_unshift($result,array("Name","Average score per game"));
-  echo output_table($result,"Points per game","stats");
+  array_unshift($result,array("Name","Average score per game","Total Points","Number of games"));
+  echo output_table($result,"Points per game (need more than 10 games)","stats","ScoreTable");
 
   /*
- how often is the last trick a non-trump trick
-  */
  * how often is the last trick a non-trump trick
+   */
 
   /* needs this so that all tables are within the div and don't float around */
   echo "<p style=\"clear:both;\">&nbsp;</p>\n";