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

diff --git a/create_database.sql b/create_database.sql
index 4be13de954a2a4ac183b5a2c9df3f2ae48a6e213..c5de955f5e02e18de3d71279bec86e1c6ed690fa 100644 (file)
--- 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,
 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',
   `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;
 
   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',
   `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;
 
   UNIQUE KEY `id` (`id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 

diff --git a/db.php b/db.php
index a78afb6aa744d58550385e0084abc56e69978ddd..1c20b8e2051272b685e055e41d9709ee7256c2a7 100644 (file)
--- a/db.php
+++ b/db.php
@@ -466,7 +466,7 @@ function DB_get_current_trickid($gameid)
   
   if(!$sequence || $sequence==4)
     {
   
   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++;
       $trickid  = mysql_insert_id();
       $sequence = 1;
       $number++;

diff --git a/functions.php b/functions.php
index 7ee180e946b9d2f8c248b9062e428b1d866ac61d..deadf34fdb03ea93fc61637ee4f1298bfea85b05 100644 (file)
--- a/functions.php
+++ b/functions.php
@@ -283,72 +283,6 @@ function count_trump($cards)
   return $trump;
 }
 
   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;
 function  create_array_of_random_numbers($useridA,$useridB,$useridC,$useridD)
 {
   global $debug;

diff --git a/index.php b/index.php
index eee9b462f8c906f7f913a585bd5dbb4b2d4b56a7..244ceb9ac7e7b22eb6a53d32ce3b193bffb746cb 100644 (file)
--- 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"))
              
              /* check for coment */
              if(myisset("comment"))


@@ -1404,40 +1428,45 @@ else if(myisset("me"))
                  $done=0;
              
              if($done)
                  $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 */
                  /* 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";
 
                  $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";
                  $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);
                    }
                }
                      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
            {
            }
          else
            {


@@ -1611,21 +1594,28 @@ else if(myisset("me"))
        {
          echo "the game is over now...<br />\n";
          
        {
          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";
 
          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";
                                " GROUP BY Hand.party" );
          echo "Totals:<br />\n".
            " <table> \n";