CLEANUP: counting points, prepare for keeping track of game points

use the database now to add up points, cleaned up the code a bit (counting
points was done in different places), don't use score table for points from cards any more

4 files changed, 79 insertions, 150 deletions

diff --git a/create_database.sql b/create_database.sql
index 4be13de..c5de955 100644
--- a/create_database.sql
+++ b/create_database.sql
@@ -204,9 +204,13 @@ UNLOCK TABLES;
 DROP TABLE IF EXISTS `Score`;
 CREATE TABLE `Score` (
   `id` int(11) NOT NULL auto_increment,
+  `create_date` timestamp NOT NULL default '0000-00-00 00:00:00',
   `game_id` int(11) NOT NULL default '0',
   `hand_id` int(11) NOT NULL default '0',
-  `score` tinyint(4) default NULL,
+  `score` enum('120', 'call120','against120','90','call90','against90',
+          '60','call60','against60','30','call30','against30',
+          '0','call0','against0',
+          'fox','karlchen','dulle','doko','againstqueens') default NULL,
   UNIQUE KEY `id` (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
@@ -230,6 +234,7 @@ CREATE TABLE `Trick` (
   `create_date` timestamp NOT NULL default '0000-00-00 00:00:00',
   `id` int(11) NOT NULL auto_increment,
   `game_id` int(11) NOT NULL default '0',
+  `winner`  tinyint(4) default NULL,
   UNIQUE KEY `id` (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
diff --git a/db.php b/db.php
index a78afb6..1c20b8e 100644
--- a/db.php
+++ b/db.php
@@ -466,7 +466,7 @@ function DB_get_current_trickid($gameid)
   
   if(!$sequence || $sequence==4)
     {
-      mysql_query("INSERT INTO Trick VALUES (NULL,NULL,NULL, ".DB_quote_smart($gameid).")");
+      mysql_query("INSERT INTO Trick VALUES (NULL,NULL,NULL, ".DB_quote_smart($gameid).",NULL)");
       $trickid  = mysql_insert_id();
       $sequence = 1;
       $number++;
diff --git a/functions.php b/functions.php
index 7ee180e..deadf34 100644
--- a/functions.php
+++ b/functions.php
@@ -283,72 +283,6 @@ function count_trump($cards)
   return $trump;
 }
 
-function card_value($card)
-{
-  switch($card)
-    {
-    case 3:     /* clubes */	 
-    case 4:	                 
-    case 5:     /* spades */	 
-    case 6:	                 
-    case 7:     /* hearts */	 
-    case 8:	                 
-    case 9:     /* diamonds */	 
-    case 10:                     
-      return 3;
-    case 11:    /* clubes */	 
-    case 12:	                 
-    case 13:	/* spades */	 
-    case 14:	                 
-    case 15:	/* hearts */	 
-    case 16:	                 
-    case 17:	/* diamonds */	 
-    case 18:
-      return 2;	                 
-    case 19:    /* diamonds */ 
-    case 20:	               
-    case 27:    /* clubs */    
-    case 28:	               
-    case 35:    /* spades */   
-    case 36:	               
-    case 43:    /* hearts */   
-    case 44:                   
-      return 11;
-    case 1:      /* heart */
-    case 2:
-    case 21:    /* diamonds */    
-    case 22:
-    case 29:    /* clubs */
-    case 30:
-    case 37:    /* spades */
-    case 38:
-      return 10;
-    case 23:    /* diamonds */ 
-    case 24:	               
-    case 31:	/* clubs */    
-    case 32:	               
-    case 39:	/* spades */   
-    case 40:	               
-    case 45:	/* hearts */   
-    case 46:	               
-      return 4;
-    case 25:    /* diamonds */   
-    case 26:	               
-    case 33:	/* clubs */    
-    case 34:	               
-    case 41:	/* spades */   
-    case 42:	               
-    case 47:	/* hearts */   
-    case 48:	               
-      return 0;
-    default:
-      myerror("something went wrong, please contact $ADMIN_NAME at $ADMIN_EMAIL. ErrorCode: 2 - $card<br>");
-      echo "something went wrong, please contact $ADMIN_NAME at $ADMIN_EMAIL. ErrorCode: 2 - $card<br>";
-      return 0;
-    }
-}
-
-
 function  create_array_of_random_numbers($useridA,$useridB,$useridC,$useridD)
 {
   global $debug;
diff --git a/index.php b/index.php
index eee9b46..244ceb9 100644
--- a/index.php
+++ b/index.php
@@ -1370,6 +1370,30 @@ else if(myisset("me"))
 			}
 		    }
 		}
+
+	      /* if sequence == 4, set winner of the trick, count points and set the next player */
+	      if($sequence==4)
+		{
+		  $play   = DB_get_cards_by_trick($trickid);
+		  $winner = get_winner($play,$gametype); /* returns the position */
+
+		  if($winner>0)
+		    mysql_query("UPDATE Trick SET winner='$winner' WHERE id='$trickid'");
+		  else
+		    echo "ERROR during scoring";
+
+		  if($debug)
+		    echo "DEBUG: position $winner won the trick <br />";
+
+		  /* who is the next player? */
+		  $next = $winner;
+		}
+	      else
+		{
+		  $next = DB_get_pos_by_hash($me)+1;
+		}
+	      if($next==5) $next=1;
+
 	      
 	      /* check for coment */
 	      if(myisset("comment"))
@@ -1404,40 +1428,45 @@ else if(myisset("me"))
 		  $done=0;
 	      
 	      if($done)
+		DB_set_game_status_by_gameid($gameid,"gameover");
+
+	      /* email next player, if game is still running */
+	      if(DB_get_game_status_by_gameid($gameid)=='play')
+		{
+		  $next_hash = DB_get_hash_from_game_and_pos($gameid,$next);
+		  $email     = DB_get_email_by_hash($next_hash);
+		  $who       = DB_get_userid_by_email($email);
+		  DB_set_player_by_gameid($gameid,$who);
+		  
+		  $message = "A card has been played in game $gameid.\n\n".
+		    "It's your turn  now.\n".
+		    "Use this link to play a card: ".$host."?me=".$next_hash."\n\n" ;
+		  mymail($email,$EmailName."a card has been played in game $gameid",$message);
+		}
+	      else /* send out final email */
 		{
-		  DB_set_game_status_by_gameid($gameid,"gameover");
-		  /* get score for last trick 
-		   * all other tricks are handled a few lines further down*/
-		  $play   = DB_get_cards_by_trick($trickid);
-		  $winner = get_winner($play,$gametype); /* returns the position */
-		  /* get points of last trick and save it */
-		  $points = 0;
-		  foreach($play as $card)
-		    $points = $points + card_value($card["card"]);
-		  $winnerid = DB_get_handid_by_gameid_and_position($gameid,$winner);
-		  if($winnerid>0)
-		    mysql_query("INSERT INTO Score VALUES (NULL, '$gameid', '$winnerid', '$points')");
-		  else
-		    {
-		      myerror("ERROR during scoring");
-		    }		  
-		  /* email all players */
 		  /* individual score */
-		  $result = mysql_query("SELECT fullname, IFNULL(SUM(score),0), Hand.party FROM Hand".
-					" LEFT JOIN Score ON Hand.id=Score.hand_id".
-					" LEFT JOIN User ON Hand.user_id=User.id".
-					" WHERE Hand.game_id=$gameid".
-					" GROUP BY fullname" );
+		  $result = mysql_query("SELECT User.fullname, IFNULL(SUM(Card.points),0), Hand.party FROM Hand".
+				" LEFT JOIN Trick ON Trick.winner=Hand.position AND Trick.game_id=Hand.game_id".
+				" LEFT JOIN User ON User.id=Hand.user_id".
+				" LEFT JOIN Play ON Trick.id=Play.trick_id".
+				" LEFT JOIN Hand_Card ON Hand_Card.id=Play.hand_card_id".
+				" LEFT JOIN Card ON Card.id=Hand_Card.card_id".
+				" WHERE Hand.game_id='$gameid'".
+				" GROUP BY User.fullname" );
 		  $message  = "The game is over. Thanks for playing :)\n";
 		  $message .= "Final score:\n";
 		  while( $r = mysql_fetch_array($result,MYSQL_NUM))
 		    $message .= "   ".$r[0]."(".$r[2].") ".$r[1]."\n";
 
-		  $result = mysql_query("SELECT Hand.party, IFNULL(SUM(score),0) FROM Hand".
-					" LEFT JOIN Score ON Hand.id=Score.hand_id".
-					" LEFT JOIN User ON Hand.user_id=User.id".
-					" WHERE Hand.game_id=$gameid".
-					" GROUP BY Hand.party" );
+		  $result = mysql_query("SELECT  Hand.party, IFNULL(SUM(Card.points),0) FROM Hand".
+				" LEFT JOIN Trick ON Trick.winner=Hand.position AND Trick.game_id=Hand.game_id".
+				" LEFT JOIN User ON User.id=Hand.user_id".
+				" LEFT JOIN Play ON Trick.id=Play.trick_id".
+				" LEFT JOIN Hand_Card ON Hand_Card.id=Play.hand_card_id".
+				" LEFT JOIN Card ON Card.id=Hand_Card.card_id".
+				" WHERE Hand.game_id='$gameid'".
+				" GROUP BY User.fullname" );
 		  $message .= "\nTotals:\n";
 		  while( $r = mysql_fetch_array($result,MYSQL_NUM))
 		    $message .= "    ".$r[0]." ".$r[1]."\n";
@@ -1461,52 +1490,6 @@ else if(myisset("me"))
 		      mymail($To,$EmailName."game over (game $gameid) part 2(2)",$link);
 		    }
 		}
-	      
-	      
-	      /* email next player */
-	      if(DB_get_game_status_by_gameid($gameid)=='play')
-		{
-		  if($sequence==4)
-		    {
-		      $play   = DB_get_cards_by_trick($trickid);
-		      $winner = get_winner($play,$gametype); /* returns the position */
-		      
-		      /* get points of last trick and save it, last trick is handled 
-		       * a few lines further up  */
-		      $points = 0;
-		      foreach($play as $card)
-			$points = $points + card_value($card["card"]);
-		      
-		      $winnerid = DB_get_handid_by_gameid_and_position($gameid,$winner);
-		      if($winnerid>0)
-			mysql_query("INSERT INTO Score VALUES (NULL, '$gameid', '$winnerid', '$points')");
-		      else
-			{
-			  myerror("ERROR during scoring");
-			};
-		      if($debug)
-			echo "DEBUG: $winner got $points <br />";
-		      
-		      /* who is the next player? */
-		      $next = $winner;
-		    }
-		  else
-		    {
-		      $next = DB_get_pos_by_hash($me)+1;
-		    }
-		  if($next==5) $next=1;
-		  
-		  /* email next player */
-		  $next_hash = DB_get_hash_from_game_and_pos($gameid,$next);
-		  $email     = DB_get_email_by_hash($next_hash);
-		  $who       = DB_get_userid_by_email($email);
-		  DB_set_player_by_gameid($gameid,$who);
-
-		  $message = "A card has been played in game $gameid.\n\n".
-		    "It's your turn  now.\n".
-		    "Use this link to play a card: ".$host."?me=".$next_hash."\n\n" ;
-		  mymail($email,$EmailName."a card has been played in game $gameid",$message);		  
-		}
 	    }
 	  else
 	    {
@@ -1611,21 +1594,28 @@ else if(myisset("me"))
 	{
 	  echo "the game is over now...<br />\n";
 	  
-	  $result = mysql_query("SELECT fullname, IFNULL(SUM(score),0), Hand.party FROM Hand".
-				" LEFT JOIN Score ON Hand.id=Score.hand_id".
-				" LEFT JOIN User ON Hand.user_id=User.id".
-				" WHERE Hand.game_id=$gameid".
-				" GROUP BY fullname" );
+	  $result = mysql_query("SELECT User.fullname, IFNULL(SUM(Card.points),0), Hand.party FROM Hand".
+				" LEFT JOIN Trick ON Trick.winner=Hand.position AND Trick.game_id=Hand.game_id".
+				" LEFT JOIN User ON User.id=Hand.user_id".
+				" LEFT JOIN Play ON Trick.id=Play.trick_id".
+				" LEFT JOIN Hand_Card ON Hand_Card.id=Play.hand_card_id".
+				" LEFT JOIN Card ON Card.id=Hand_Card.card_id".
+				" WHERE Hand.game_id='$gameid'".
+				" GROUP BY User.fullname" );
 	  echo "Final Score:<br />\n".
 	    " <table>\n";;
 	  while( $r = mysql_fetch_array($result,MYSQL_NUM))
 	    echo "  <tr><td>  ".$r[0]."</td><td>(".$r[2].")</td><td> ".$r[1]."</td></tr>";
 	  echo "</table>\n";
 
-	  $result = mysql_query("SELECT Hand.party, IFNULL(SUM(score),0) FROM Hand".
-				" LEFT JOIN Score ON Hand.id=Score.hand_id".
-				" LEFT JOIN User ON Hand.user_id=User.id".
-				" WHERE Hand.game_id=$gameid".
+
+	  $result = mysql_query("SELECT Hand.party, IFNULL(SUM(Card.points),0) FROM Hand".
+				" LEFT JOIN Trick ON Trick.winner=Hand.position AND Trick.game_id=Hand.game_id".
+				" LEFT JOIN User ON User.id=Hand.user_id".
+				" LEFT JOIN Play ON Trick.id=Play.trick_id".
+				" LEFT JOIN Hand_Card ON Hand_Card.id=Play.hand_card_id".
+				" LEFT JOIN Card ON Card.id=Hand_Card.card_id".
+				" WHERE Hand.game_id='$gameid'".
 				" GROUP BY Hand.party" );
 	  echo "Totals:<br />\n".
 	    " <table> \n";