+ }
+
+ /* add number of active games */
+ $result = DB_query_array_all("SELECT user_id, COUNT(*) as c " .
+ " FROM Hand".
+ " LEFT JOIN Game ON Game.id=game_id".
+ " WHERE Game.status IN ('pre','play')".
+ " GROUP BY user_id");
+
+ foreach($result as $res)
+ {
+ $player[$res[0]]['active'] = $res[1];
+ }
+
+ /* response time of users*/
+ $result = DB_query_array_all("SELECT user_id,".
+ "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 ".
+ "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 ".
+ "GROUP BY user_id ");
+
+ foreach($result as $res)
+ {
+ $player[$res[0]]['response'] = $res[1];
+ }
+
+ /* most solos */
+ $result = DB_query_array_all("SELECT user_id as uid,".
+ " COUNT(*), ".
+ " COUNT(*)/(SELECT COUNT(*) FROM Hand LEFT JOIN User ON User.id=Hand.user_id WHERE User.id=uid) as c ".
+ " FROM Game ".
+ " LEFT JOIN Hand ON Hand.position=startplayer AND Game.id=Hand.game_id ".
+ " WHERE type='solo' AND Game.status='gameover' ".
+ " GROUP BY user_id ");
+
+ foreach($result as $res)
+ {
+ $player[$res[0]]['solo'] = $res[1];
+ $player[$res[0]]['soloavg'] = $res[2];
+ }
+
+
+ /* sort everything nicely */
+
+ function cmp($a,$b)
+ {
+ if($a['nr']==0) return 1;
+ if($b['nr']==0) return 1;
+
+ $a=$a['points']/$a['nr'];
+ $b=$b['points']/$b['nr'];
+
+ if ($a == $b)
+ return 0;
+ return ($a > $b) ? -1 : 1;
+ }
+ usort($player,'cmp');
+
+
+ foreach($player as $pl)
+ {
+ /* limit to players with at least 10 games */
+ if($pl['nr']>10)
+ $return[] = array( $pl['name'], round($pl['points']/$pl['nr'],3), $pl['points'],$pl['nr'],$pl['active'],
+ $pl['response'],$pl['solo'],$pl['soloavg']);
+ }
+
+ return $return;
+}
+
+function format_score_table_ascii($score)
+{
+ $output="";
+ if(sizeof($score)==0)
+ return "";
+
+ /* truncate table if we have too many games */
+ $max = sizeof($score);
+ if($max>6) $output.=" (table truncated to last 6 games)\n";
+
+ /* output header */
+ foreach($score[0]['players'] as $id=>$points)
+ {
+ $name = DB_get_name('userid',$id); /*TODO*/
+ $output.= " ".substr($name,0,2)." |";
+ }
+ $output.=" P |\n";
+ $output.= "------+------+------+------+------+\n";
+
+ /* output score for each game */
+ $i=0;
+ foreach($score as $game)
+ {
+ $i++;
+ if($i-1<$max-6) continue;
+
+ foreach($game['players'] as $id=>$points)
+ $output.=str_pad($points,6," ",STR_PAD_LEFT)."|";
+ $output.=str_pad($game['points'],4," ",STR_PAD_LEFT);
+
+ /* check for solo */
+ if($game['solo'])
+ $output.= " S|";
+ else
+ $output.= " |";
+
+ $output.="\n";
+ }
+ return $output;
+}
+
+function format_score_table_html($score,$userid)
+{
+ global $INDEX;
+
+ if(sizeof($score)==0)
+ return "";
+
+ $output = "<div class=\"scoretable\">\n<table class=\"score\">\n";
+
+ /* output header */
+ $header = "";
+ $header.= " <thead>\n <tr>\n";
+ $header.= " <th> No </th>";
+ foreach($score[0]['players'] as $id=>$points)
+ {
+ $name = DB_get_name('userid',$id); /*TODO*/
+ $header.= "<th> ".substr($name,0,2)." </th>";
+ }
+ $header.="<th>P</th>\n </tr>\n </thead>\n";
+
+ /* use the same as footer */
+ $footer = "";
+ $footer.= " <tfoot>\n <tr>\n";
+ $footer.= " <td> No </td>";
+ foreach($score[0]['players'] as $id=>$points)
+ {
+ $name = DB_get_name('userid',$id); /*TODO*/
+ $footer.= "<td> ".substr($name,0,2)." </td>";
+ }
+ $footer.="<td>P</td>\n </tr>\n </tfoot>\n";
+
+ /* body */
+ $body = "";
+ $body.= " <tbody>\n";
+ $i=0;
+ foreach($score as $game)
+ {
+ $i++;
+ $body.=" <tr>";
+ $userhash = DB_get_hash_from_gameid_and_userid($game['gameid'],$userid);
+ /* create link to old games only if you are logged in and its your game*/
+ if(isset($_SESSION['id']) && $_SESSION['id']==$userid)
+ $body.=" <td> <a href=\"".$INDEX."?action=game&me=".$userhash."\">$i</a></td>";
+ else
+ $body.=" <td>$i</td>";
+
+ foreach($game['players'] as $id=>$points)
+ $body.="<td>".$points."</td>";
+ $body.="<td>".$game['points'];