sp1.sql

set character set utf8;

-- deprecated (deleted)
DROP PROCEDURE IF EXISTS odds_old_1x2;
DROP PROCEDURE IF EXISTS odds_old_moneyline;
DROP PROCEDURE IF EXISTS odds_old_underover;
DROP PROCEDURE IF EXISTS odds_old_handicap;
DROP PROCEDURE IF EXISTS split_string1;
DROP PROCEDURE IF EXISTS split_string2;

--correct_url (inc/functions.inc.php)
DELIMITER |
DROP PROCEDURE IF EXISTS correct_url;
CREATE PROCEDURE correct_url(game_id INT, lang_id INT)
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;
	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT m.URL AS aLeagueURL, s.URL AS aSportURL, c.URL AS aCountryURL
	FROM
		BE_Game AS g,
		BE_Match AS m,
		BE_Sport AS s,
		BE_League AS l,
		BE_Country AS c
	WHERE
		g.GameID = game_id
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND m.SportID = s.SportID AND s.LangID = lang_id
		AND m.LeagueID = l.LeagueID
		AND l.CountryID = c.CountryID AND c.LangID = lang_id;

	IF((SELECT COUNT(*) FROM BE_TMP_Game) = 0) THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
		SELECT m.URL AS aLeagueURL, s.URL AS aSportURL, c.URL AS aCountryURL
		FROM
			BE_GameArchive AS g,
			BE_Match AS m,
			BE_Sport AS s,
			BE_League AS l,
			BE_Country AS c
		WHERE
			g.GameID = game_id
			AND g.MatchID = m.MatchID AND m.LangID = lang_id
			AND m.SportID = s.SportID AND s.LangID = lang_id
			AND m.LeagueID = l.LeagueID
			AND l.CountryID = c.CountryID AND c.LangID = lang_id;
	END IF;

	SELECT * FROM BE_TMP_Game;

	DROP TABLE BE_TMP_Game;
END|
DELIMITER ;

--get_mutual_team (mutual)
DELIMITER |
DROP PROCEDURE IF EXISTS get_mutual_team;
CREATE PROCEDURE get_mutual_team(league_id INT, lang_id INT)
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT tg1.TeamID, tg1.Name
	FROM
		BE_Game AS g,
		BE_TeamGroup AS tg1 
	WHERE
		g.MatchID = league_id
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
	GROUP BY tg1.Name;

	IF((SELECT COUNT(*) FROM BE_TMP_Game) = 0) THEN
		CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
		SELECT tg1.TeamID, tg1.Name
		FROM
			BE_GameArchive AS g,
			BE_TeamGroup AS tg1 
		WHERE
			g.MatchID = league_id
			AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		GROUP BY tg1.Name;
	END IF;

	SELECT * FROM BE_TMP_Game ORDER BY Name;

	DROP TABLE BE_TMP_Game;
END|
DELIMITER ;

--odds_1x2 (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_1x2;
CREATE PROCEDURE odds_1x2(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2, g.K0, g.K10, g.K02,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2, IF(og.K0 IS NULL OR og.K0 = 0, g.K0, og.K0) AS aOldK0,
								IF(og.K10 IS NULL OR og.K10 = 0, g.K10, og.K10) AS aOldK10, IF(og.K02 IS NULL OR og.K02 = 0, g.K02, og.K02) AS aOldK02,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course AS g
									LEFT JOIN BE_CourseOld AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

--	DROP TABLE IF EXISTS BE_TMP_OddsOld;

--	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
--	SELECT o.idCourse, o.CourseID, o.K1, o.K0, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
--	FROM
--		BE_TMP_Odds AS tmp,
--		BE_CourseOld AS o
--	WHERE
--		tmp.idCourse = o.CourseID;
END|
DELIMITER ;

--odds_underover (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_underover;
CREATE PROCEDURE odds_underover(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, g.`Value`, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course2 AS g
									LEFT JOIN BE_Course2Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course2Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--odds_handicap (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_handicap;
CREATE PROCEDURE odds_handicap(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, g.`Value`, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course3 AS g
									LEFT JOIN BE_Course3Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course3Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--odds_moneyline (nextmatch)
DELIMITER |
DROP PROCEDURE IF EXISTS odds_moneyline;
CREATE PROCEDURE odds_moneyline(league_id INT, lang_id INT, project_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Odds;

	SET @ldb = (SELECT DBPostfix FROM BE_Project WHERE idProject = project_id LIMIT 1);

	SET @query = CONCAT("CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Odds ENGINE=MEMORY
							SELECT
								g.idCourse, o.OfficeID, o.Name AS aBettingOffice, d.Description, o.OriginID, u.URL,
								g.BettingOfficeId, g.K1, g.K2,
								DATE_FORMAT(CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i')  AS aCreated,
								IF(og.K1 IS NULL OR og.K1 = 0, g.K1, og.K1) AS aOldK1, IF(og.K2 IS NULL OR og.K2 = 0, g.K2, og.K2) AS aOldK2,
								DATE_FORMAT(IF(og.Created IS NULL OR og.Created = 0, CONVERT_TZ(g.Created, '+1:00', '", timezone, "'), CONVERT_TZ(og.Created, '+1:00', '", timezone, "')), '%d/%m %H:%i')  AS aOldCreated
							FROM
								BE_Course4 AS g
									LEFT JOIN BE_Course4Old AS og ON (g.idCourse = og.CourseID),
								BE_Office AS o
									LEFT JOIN BE_OfficeDescription AS d ON (o.idOffice = d.OfficeID AND d.LangID = ", lang_id, ")
									LEFT JOIN betexplorer_", @ldb, ".BettingOfficeURL AS u ON (u.OfficeID = o.OfficeID)
							WHERE
								g.GameID = ", league_id, "
								AND g.BettingOfficeID = o.OfficeID
							GROUP BY
								g.idCourse
							ORDER BY
								og.idCourse;");

	PREPARE course FROM @query;
	EXECUTE course;
	DEALLOCATE PREPARE course;

	DROP TABLE IF EXISTS BE_TMP_OddsOld;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_OddsOld ENGINE=MEMORY
	SELECT o.idCourse, o.CourseID, o.K1, o.K2, o.Created, DATE_FORMAT(CONVERT_TZ(o.Created, '+1:00', '", timezone, "'), '%d/%m %H:%i') AS aCreated
	FROM
		BE_TMP_Odds AS tmp,
		BE_Course4Old AS o
	WHERE
		tmp.idCourse = o.CourseID;

END|
DELIMITER ;

--teaminfo1 (teaminfo.php)
DELIMITER |
DROP PROCEDURE IF EXISTS teaminfo1;
CREATE PROCEDURE teaminfo1(league_id INT, team_id INT, lang_id INT, timezone CHAR(6))
MODIFIES SQL DATA
BEGIN
	DROP TABLE IF EXISTS BE_TMP_Game;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT
		g.GameID, DATE_FORMAT(CONVERT_TZ(g.`Date`, '+1:00', timezone), '%d.%m.%Y') AS aDate, g.`Date`, g.Round, g.HomeID, g.GuestID,
		tg1.Name AS aHomeName, tg2.Name AS aGuestName, tg1.URL AS aHomeURL, tg2.URL AS aGuestURL
	FROM
		BE_Game AS g,
		BE_Match AS m,
		BE_TeamGroup AS tg1,
		BE_TeamGroup AS tg2
	WHERE
		g.MatchID = league_id
		AND g.Played = 'n'
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND (g.HomeID = team_id OR g.GuestID = team_id)
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		AND g.GuestID = tg2.TeamID AND tg2.LangID = lang_id;

	CREATE TEMPORARY TABLE IF NOT EXISTS BE_TMP_Game ENGINE=MEMORY
	SELECT
		g.GameID, DATE_FORMAT(CONVERT_TZ(g.`Date`, '+1:00', timezone), '%d.%m.%Y') AS aDate, g.`Date`, g.Round, g.HomeID, g.GuestID,
		tg1.Name AS aHomeName, tg2.Name AS aGuestName, tg1.URL AS aHomeURL, tg2.URL AS aGuestURL
	FROM
		BE_GameArchive AS g,
		BE_Match AS m,
		BE_TeamGroup AS tg1,
		BE_TeamGroup AS tg2
	WHERE
		g.MatchID = league_id
		AND g.Played = 'n'
		AND g.MatchID = m.MatchID AND m.LangID = lang_id
		AND (g.HomeID = team_id OR g.GuestID = team_id)
		AND g.HomeID = tg1.TeamID AND tg1.LangID = lang_id
		AND g.GuestID = tg2.TeamID AND tg2.LangID = lang_id;

	SELECT * FROM BE_TMP_Game ORDER BY `Date` DESC;
	DROP TABLE IF EXISTS BE_TMP_Game;
END|
DELIMITER ;

sp2.sql

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 ;
-- }}}