USE tenisportal;
SET CHARACTER SET utf8;

-- pl_matches() {{{
-- get players matches
DELIMITER |
DROP PROCEDURE IF EXISTS pl_matches;
CREATE PROCEDURE pl_matches(player_id INT, played CHAR(1), lang_id INT, timezone VARCHAR(5), lim INT)
MODIFIES SQL DATA
BEGIN
	CASE played
		WHEN 'y' THEN SET @played = 'y';
		WHEN 'n' THEN SET @played = 'n';
		ELSE SET @played = NULL;
	END CASE;

	IF (lim IS NOT NULL OR lim != 0) THEN
		SET @lim = CONCAT(" LIMIT ", lim);
	ELSE
		SET @lim = (SELECT CHAR(32));
	END IF;

	SET @game = (SELECT GROUP_CONCAT(tg.idGame) FROM TournamentGame AS tg, GamePlayer AS gp WHERE tg.Played = @played AND tg.idGame = gp.GameID AND gp.PlayerID = player_id);

	IF (@game IS NOT NULL) THEN

		SET @query = CONCAT("SELECT tg.idGame,
								tg.Score, tg.Revert, tg.Result1, tg.Result2,
								tl.TournamentID, tl.Name AS aTournamentName, tl.URL AS aTournamentURL,
								DATE_FORMAT(CONVERT_TZ(CONCAT(tg.Date, ' ', IFNULL(tg.Time, '00:00:00')), '", timezone, "', '+1:00'), '%d. %m. %Y %H:%i') AS aDate,
								CONCAT(pl.Surname, ' ', pl.Firstname) AS aPlayerName
							FROM
								GamePlayer AS gp,
								TournamentGame AS tg,
								Tournament AS t,
								TournamentLN AS tl,
								PlayerLN AS pl
							WHERE
								tg.idGame IN (", @game, ")
								AND gp.GameID = tg.idGame
								AND t.idTournament = tl.TournamentID
								AND t.Active = 'y'
								AND tl.TournamentID = tg.TournamentID
								AND tg.Played = '", @played, "'
								AND pl.PlayerID = gp.PlayerID
								AND pl.LangID = '", lang_id, "'
							", @lim);

		PREPARE m_query FROM  @query;
		EXECUTE m_query;
		DEALLOCATE PREPARE m_query;

	END IF;
END|
DELIMITER ;
-- }}}

-- odds() {{{
-- get odds
DELIMITER |
DROP PROCEDURE IF EXISTS odds;
CREATE PROCEDURE odds(game_id TEXT)
MODIFIES SQL DATA
BEGIN
	DROP TEMPORARY TABLE IF EXISTS TMP_Odds;

	SET @query = CONCAT("CREATE TEMPORARY TABLE TMP_Odds
						SELECT
							GameID, ROUND(AVG(K1), 2) AS aK1, ROUND(AVG(K2), 2) AS aK2
						FROM Odds12
						WHERE GameID IN (", game_id, ")
						GROUP BY GameID");

	PREPARE m_query FROM @query;
	EXECUTE m_query;
	DEALLOCATE PREPARE m_query;

	SELECT * FROM TMP_Odds GROUP BY GameID;

	DROP TEMPORARY TABLE IF EXISTS TMP_Odds;
END|
DELIMITER ;
-- }}}