internal static void UpdateMatchRecord(int matchID, MatchObject match, decimal vigHome, decimal vigDraw, decimal vigAway, decimal betfairHome, decimal betfairDraw, decimal betfairAway, decimal valueHome, decimal valueDraw, decimal valueAway, DateTime pinnacleLastUpdated)
{
var sql = ("UPDATE Match SET " +
"LastUpdated = CONVERT(DATETIME2,'" + pinnacleLastUpdated + "',105), " +
"PinnacleHome = '" + match.PinnacleHomeOdds + "'," +
"PinnacleDraw = '" + match.PinnacleDrawOdds + "'," +
"PinnacleAway = '" + match.PinnacleAwayOdds + "'," +
"VigFreeHome = '" + vigHome + "'," +
"VigFreeDraw = '" + vigDraw + "'," +
"VigFreeAway = '" + vigAway + "'," +
"BetfairHome = '" + betfairHome + "'," +
"BetfairDraw = '" + betfairDraw + "'," +
"BetfairAway = '" + betfairAway + "'," +
"ValueHome = '" + valueHome + "'," +
"ValueDraw = '" + valueDraw + "'," +
"ValueAway = '" + valueAway + "'" +
"WHERE MatchID = '" + matchID + "'");
SqlConnection con;
SqlCommand command;
con = new SqlConnection(connectionString);
con.Open();
command = new SqlCommand(sql, con);
command.ExecuteNonQuery();
con.Close();
}
internal static void AddMatchRecord(MatchObject match, decimal vigHome, decimal vigDraw, decimal vigAway, decimal betfairHome, decimal betfairDraw, decimal betfairAway, decimal valueHome, decimal valueDraw, decimal valueAway, DateTime pinnacleLastUpdated, DateTime commenceTime)
{
var sql = ("INSERT INTO Match (League, HomeTeam, AwayTeam, CommenceTime, LastUpdated, PinnacleHome, PinnacleDraw, PinnacleAway, " +
"VigFreeHome, VigFreeDraw, VigFreeAway, BetfairHome, BetfairDraw, BetfairAway, ValueHome, ValueDraw, ValueAway, Completed) VALUES(" +
"'" + match.League + "', " +
"'" + match.HomeTeam + "', " +
"'" + match.AwayTeam + "', " +
"CONVERT(DATETIME2, '" + commenceTime + "', 105), " +
"CONVERT(DATETIME2, '" + pinnacleLastUpdated + "', 105), " +
"'" + match.PinnacleHomeOdds + "', " +
"'" + match.PinnacleDrawOdds + "', " +
"'" + match.PinnacleAwayOdds + "', " +
"'" + vigHome + "', " +
"'" + vigDraw + "', " +
"'" + vigAway + "', " +
"'" + betfairHome + "', " +
"'" + betfairDraw + "', " +
"'" + betfairAway + "', " +
"'" + valueHome + "', " +
"'" + valueDraw + "', " +
"'" + valueAway + "', " +
"0)");
SqlConnection con;
SqlCommand command;
con = new SqlConnection(connectionString);
con.Open();
command = new SqlCommand(sql, con);
command.ExecuteNonQuery();
con.Close();
}
internal void ProcessMatch(MatchObject match)
{
var startTimeDouble = Convert.ToDouble(match.CommenceTime);
var pinnacleLastUpdatedDouble = Convert.ToDouble(match.PinnacleLastUpdated);
var betfairLastUpdatedDouble = Convert.ToDouble(match.BetfairLastUpdated);
var startTime = new DateTime(1970, 1, 1).AddSeconds(startTimeDouble);
var pinnacleLastUpdated = new DateTime(1970, 1, 1).AddSeconds(pinnacleLastUpdatedDouble);
var betfairLastUpdated = new DateTime(1970, 1, 1).AddSeconds(betfairLastUpdatedDouble);
if (match.PinnacleHomeOdds > 0.0m && match.BetfairHomeOdds > 0.0m && Convert.ToInt64(match.PinnacleLastUpdated) < Convert.ToInt64(match.CommenceTime))
{
decimal vig = CalculateOdds.getVig(match);
decimal vigHome = CalculateOdds.getVigOdds(match.PinnacleHomeOdds, vig);
decimal vigDraw = CalculateOdds.getVigOdds(match.PinnacleDrawOdds, vig);
decimal vigAway = CalculateOdds.getVigOdds(match.PinnacleAwayOdds, vig);
decimal betfairHome = CalculateOdds.getBetfairOdds(match.BetfairHomeOdds);
decimal betfairDraw = CalculateOdds.getBetfairOdds(match.BetfairDrawOdds);
decimal betfairAway = CalculateOdds.getBetfairOdds(match.BetfairAwayOdds);
decimal valueHome = CalculateOdds.getValueOdds(vigHome, betfairHome);
decimal valueDraw = CalculateOdds.getValueOdds(vigDraw, betfairDraw);
decimal valueAway = CalculateOdds.getValueOdds(vigAway, betfairAway);
if (DatabaseCalls.DoesRecordExist(match, startTime))
{
int matchID = DatabaseCalls.getMatchRecordID(match, startTime);
DatabaseCalls.UpdateMatchRecord(matchID, match, vigHome, vigDraw, vigAway, match.BetfairHomeOdds, match.BetfairDrawOdds,
match.BetfairAwayOdds, valueHome, valueDraw, valueAway, pinnacleLastUpdated);
}
else
{
DatabaseCalls.AddMatchRecord(match, vigHome, vigDraw, vigAway, match.BetfairHomeOdds, match.BetfairDrawOdds,
match.BetfairAwayOdds, valueHome, valueDraw, valueAway, pinnacleLastUpdated, startTime);
//add it
//write to historic table
}
//Add to historic record anyway
DatabaseCalls.AddMatchRecordHistoric(match, vigHome, vigDraw, vigAway, match.BetfairHomeOdds, match.BetfairDrawOdds,
match.BetfairAwayOdds, valueHome, valueDraw, valueAway, pinnacleLastUpdated, startTime);
}
}
internal static int getMatchRecordID(MatchObject match, DateTime commenceTime)
{
var sql = ("SELECT MatchID FROM Match WHERE HomeTeam = '" + match.HomeTeam + "' AND AwayTeam = '" + match.AwayTeam + "' AND CommenceTime = CONVERT(DATETIME2,'" + commenceTime + "',105)");
SqlConnection con;
SqlCommand command;
SqlDataReader reader;
con = new SqlConnection(connectionString);
con.Open();
command = new SqlCommand(sql, con);
reader = command.ExecuteReader();
while (reader.Read())
{
return(reader.GetInt32(0));
}
return(0);
}
internal static bool DoesRecordExist(MatchObject match, DateTime commenceTime)
{
var sql = ("SELECT * FROM Match WHERE HomeTeam = '" + match.HomeTeam + "' AND AwayTeam = '" + match.AwayTeam + "' AND CommenceTime = CONVERT(DATETIME2,'" + commenceTime + "',105)");
SqlConnection con;
SqlCommand command;
SqlDataReader reader;
con = new SqlConnection(connectionString);
con.Open();
command = new SqlCommand(sql, con);
reader = command.ExecuteReader();
while (reader.Read())
{
return(true);
}
return(false);
}
internal static decimal getVig(MatchObject match)
{
return((1 / match.PinnacleHomeOdds) + (1 / match.PinnacleDrawOdds) + (1 / match.PinnacleAwayOdds));
}
internal List <MatchObject> GetMatchesForLeague(string league)
{
var data = JObject.Parse(league)["data"];
dynamic json = JsonConvert.SerializeObject(data);
dynamic newJson = JsonConvert.DeserializeObject(json);
List <MatchObject> MatchObject = new List <MatchObject>();
foreach (var item in newJson)
{
MatchObject matchObject = new MatchObject();
matchObject.League = item.sport_key;
matchObject.CommenceTime = item.commence_time;
string homeTeam = item.home_team;
var teams = item.teams;
var teamOne = teams[0].ToObject <string>();
var teamTwo = teams[1].ToObject <string>();
int homeTeamOdds = 0;
int awayTeamOdds = 0;
if (homeTeam == teamOne)
{
matchObject.HomeTeam = teamOne;
matchObject.AwayTeam = teamTwo;
homeTeamOdds = 0;
awayTeamOdds = 1;
}
else
{
matchObject.HomeTeam = teamTwo;
matchObject.AwayTeam = teamOne;
homeTeamOdds = 1;
awayTeamOdds = 0;
}
var odds = item.sites;
foreach (var odd in odds)
{
if (odd.site_key.ToObject <string>() == "pinnacle")
{
matchObject.PinnacleLastUpdated = odd.last_update.ToObject <string>();
var pinnacleOdds = odd.odds;
var exactOdds = pinnacleOdds.h2h;
matchObject.PinnacleHomeOdds = exactOdds[homeTeamOdds].ToObject <decimal>();
matchObject.PinnacleAwayOdds = exactOdds[awayTeamOdds].ToObject <decimal>();
matchObject.PinnacleDrawOdds = exactOdds[2].ToObject <decimal>();
}
}
foreach (var odd in odds)
{
if (odd.site_key.ToObject <string>() == "betfair")
{
matchObject.BetfairLastUpdated = odd.last_update.ToObject <string>();
var betfairOdds = odd.odds;
var exactOdds = betfairOdds.h2h;
matchObject.BetfairHomeOdds = exactOdds[homeTeamOdds].ToObject <decimal>();
matchObject.BetfairAwayOdds = exactOdds[awayTeamOdds].ToObject <decimal>();
matchObject.BetfairDrawOdds = exactOdds[2].ToObject <decimal>();
}
}
//Console.WriteLine(matchObject);
MatchObject.Add(matchObject);
}
return(MatchObject);
}