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 ;