<?

/*
 * Class: SQL_Dictinonary
 *
 * A class to provide a centralised repository of all the 
 * SQL statements used in a project.
 *
 * Use this class to encapsulate all of your SQL and specifically
 * to query the Scores database table, for use in conjunction with
 * the Scoreboard class.
 *
 * The constructor just sets up an array with all of the SQL (which
 * is easy to extend) while the getSQL() method is the principle
 * accessor method used to return the relevant query.
 *
 * Pleae note - the WEEK() statements used here define a week
 * as starting on a Monday and ending on a Sunday.
 *
 * The SQL here expects a database table as defined below:
 *
 * DROP TABLE IF EXISTS Scores;
 * CREATE TABLE Scores (
 *   ID bigint(20) unsigned NOT NULL auto_increment,
 *   UserID bigint(20) unsigned NOT NULL,
 *   Score int(10) unsigned NOT NULL,
 *   GameName varchar(255) NOT NULL,
 *   Date datetime NOT NULL,
 *   PRIMARY KEY  (ID),
 *   KEY UpdateScore (UserID,GameName),
 * ) TYPE=MyISAM;
 *
 * Distributed under the LGPL license:
 * http://www.gnu.org/licenses/lgpl.html
 * Duncan Gough
 * 3rdSense.com
 *
 * Home  http://www.suttree.com
 * Work  http://www.3rdsense.com
 * Play! http://www.playaholics.com
 */

class SQL_Dictionary {

    var $queryList;

    function SQL_Dictionary() {

        $this->queryList = array(

                        // Score submissions
                        "get_best_score_this_week" => "SELECT ID, Score AS BestScore FROM Scores WHERE GameName = ? AND UserID = ? AND YEAR( Date ) = YEAR( NOW() ) AND WEEK( Date, 1 ) = WEEK( NOW(), 1 )",
                        "submit_score" => "INSERT INTO Scores ( UserID, Score, GameName, Date ) VALUES ( ?, ?, ?, NOW() )",
                        "update_score" => "UPDATE Scores SET Score = ?, Date = NOW() WHERE UserID = ? AND GameName = ? AND ID = ?",

                        // Scoreboards per game, per week
                        "scores_this_week" => "SELECT UserID, Score, Date FROM Scores WHERE CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) ) AND GameName = ? GROUP BY GameName, UserID ORDER BY Score DESC, Date DESC",
                        "scores_last_week" => "SELECT UserID, Score, Date FROM Scores WHERE CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) ) - 1 AND GameName = ? GROUP BY GameName, UserID ORDER BY Score DESC, Date DESC",

                        // Personal scores, per week
                        "your_scores_this_week" => "SELECT UserID, GameName, Score, Date FROM Scores WHERE UserID = ? AND CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) ) GROUP BY Scores.GameName ORDER BY Scores.Score DESC, Scores.Date DESC",
                        "your_scores_last_week" => "SELECT UserID, GameName, Score, Date FROM Scores WHERE UserID = ? AND CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) ) - 1 GROUP BY Scores.GameName ORDER BY Scores.Score DESC, Scores.Date DESC",

                        // Personal ranking, per week
                        "your_ranking_this_week" => "SELECT count(*) AS Ranking FROM Scores WHERE GameName = ? AND Score >= ? AND CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) )",
                        "your_ranking_last_week" => "SELECT count(*) AS Ranking FROM Scores WHERE GameName = ? AND Score >= ? AND CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) ) - 1",

                        "equal_ranking_this_week" => "SELECT count(*) AS EqualRanking FROM Scores WHERE GameName = ? AND Score = ? AND CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) )",
                        "equal_ranking_last_week" => "SELECT count(*) AS EquaRlanking FROM Scores WHERE GameName = ? AND Score = ? AND CONCAT( YEAR( Date ), WEEK( Date, 1 ) ) = CONCAT( YEAR( NOW() ), WEEK( NOW(), 1 ) ) - 1",

        );

    }

    function getSQL( $key ) {

        if( !$this->queryList[ $key ] )
            die( "No such query => " . $key );
        else
            return $this->queryList[ $key ];

    }

}

?>