private void comboBox2_SelectedIndexChanged(object sender, EventArgs e) { var db = new soccerDBDataContext(); comboBox3.DataSource = db.Teams.Where(x => x.compName == comboBox2.Text).Select(x => x.Name); comboBox4.DataSource = db.Teams.Where(x => x.compName == comboBox2.Text).Select(x => x.Name); }
public void addTeams(List <Team> teams) { var db = new soccerDBDataContext(); var teamdb = (from t in db.Teams select t); // Todos os resultados da db var countries = (from t in db.Countries select t); // Gets the countries from db var countriesToAdd = new List <string>(); var teamsToAdd = new List <Team>(); foreach (var t in teams) { if (!teamdb.Any(p => p.Name == t.Name)) // Doesn't exist on DB { teamsToAdd.Add(t); // Adds to the list to add countriesToAdd.Add(t.Country_name); // TODO: If the team already exists most likely the country exists aswell } } var _countries = countriesToAdd.Distinct().Select(s => new Country() { Name = s }).ToList().Where(c => !countries.Any(p => p.Name == c.Name)).ToList(); db.Teams.InsertAllOnSubmit(teamsToAdd); db.Countries.InsertAllOnSubmit(_countries); db.SubmitChanges(); MessageBox.Show("Added " + teamsToAdd.Count + " teams\n" + _countries.Count + " countries to the DB from placard!"); }
public Form1() { InitializeComponent(); //endDateTextBox.Text = DateTime.ParseExact(DateTime.Today.ToString(), "d/MM/yy", null).ToString(); var db = new soccerDBDataContext(); /*var box = new ComboBox * { * DropDownStyle = ComboBoxStyle.DropDownList, * DataSource = db.Countries.Select(x => x.Name), * Name = "country", * Anchor = AnchorStyles.Left, * Dock = DockStyle.None * }; * * box.SelectedIndexChanged += new EventHandler(countryBox_SelectedIndexChanged); * * var pt = groupBox1.DisplayRectangle.Location; * pt.X += (groupBox1.DisplayRectangle.Width - box.Width) / 2; * pt.Y += (groupBox1.DisplayRectangle.Height - box.Height) / 2; * * groupBox1.Location = pt; * * groupBox1.Controls.Add(box);*/ comboBox1.DataSource = db.Countries.Select(x => x.Name); comboBox2.DataSource = db.Competitions.Where(x => x.Country_name == comboBox1.Text).Select(x => x.Name); startDateTextBox.Text = "01/08/2017"; endDateTextBox.Text = DateTime.Now.ToString("dd/MM/yyyy"); }
public bool Robinson_Over25(string home, string away, string league) { var db = new soccerDBDataContext(); var home_games = db.Matches.Where(x => x.Home_team == home && x.Competition_name == league).OrderByDescending(x => x.date).Take(3); var cont1 = 0; var cont2 = 0; foreach (var game in home_games) { cont1 += (int)game.home_goals; if (game.home_goals + game.away_goals > 2) { cont2++; } } if (cont1 >= 7 && cont2 >= 2) { var away_games = db.Matches.Where(x => x.Away_team == away && x.Competition_name == league).OrderByDescending(x => x.date).Take(3); cont1 = 0; //>=7 cont2 = 0; //>=2 var cont3 = 0; // >=2 var cont4 = 0; // >= 2 int i = 0; foreach (var game in away_games) { if (i == 0) // Its the last game { cont4 = (int)game.home_goals + (int)game.away_goals; i++; } cont1 += (int)game.away_goals; if (game.home_goals + game.away_goals > 2) { cont2++; } if (game.away_goals > 0) { cont3++; } } if (cont1 >= 7 && cont2 >= 2 && cont3 >= 2 && cont4 >= 2) { return(true); } } return(false); }
private void getCompetitionsLink() { var db = new soccerDBDataContext(); var Webget = new HtmlWeb(); ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12; var doc = Webget.Load("https://www.academiadasapostas.com/stats/list"); var classToFind = "toggle_content"; var allElementsWithClassFloat = doc.DocumentNode.SelectNodes(string.Format("//*[contains(@class,'{0}')]", classToFind)); }
public bool Robinson_Under25(string home, string away, string league) { var db = new soccerDBDataContext(); var home_games = db.Matches.Where(x => x.Home_team == home && x.Competition_name == league).OrderByDescending(x => x.date).Take(3); int cont1 = 0; int cont2 = 0; foreach (var game in home_games) { if (game.home_goals + game.away_goals < 3) { cont1++; } if (game.home_goals == 0 || game.away_goals == 0) { cont2++; } } if (cont1 >= 2 && cont2 >= 1) { var away_games = db.Matches.Where(x => x.Away_team == away && x.Competition_name == league).OrderByDescending(x => x.date).Take(3); cont1 = 0; cont2 = 0; foreach (var game in away_games) { if (game.home_goals + game.away_goals < 3) { cont1++; } if (game.away_goals == 0) { cont2++; } } if (cont1 >= 2 && cont2 >= 1) { return(true); } } return(false); }
private void button6_Click(object sender, EventArgs e) { string league = "Segunda Liga 2017/2018"; getCompetitionsLink(); var db = new soccerDBDataContext(); var Webget = new HtmlWeb(); // Todo add this ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3 | SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12; // https://www.academiadasapostas.com/stats/competition/portugal-stats/100/12625/35896 SEGUNDA LIGA var doc = Webget.Load("https://www.academiadasapostas.com/stats/competition/portugal-stats/100"); //SEGUNDA LIGA var ourNode = doc.DocumentNode.SelectNodes("//*[@id=\"s\"]/div/div/div/span[2]/table/tbody/tr"); var teams = new Dictionary <string, string>(); // CORRECT XPATH // row.SelectNodes("/html[1]/body[1]/div[12]/div[2]/div[1]/div[1]/div[1]/span[2]/table[1]/tbody[1]/tr[1]/td[3]/a") var cont = 1; foreach (var row in ourNode) { var t = row.SelectSingleNode( "/html[1]/body[1]/div[12]/div[2]/div[1]/div[1]/div[1]/span[2]/table[1]/tbody[1]/tr[" + cont++ + "]/td[3]/a"); var n = t.OuterHtml; var url = n.Split('"', '"')[1]; var sub = n.Substring(n.IndexOf("\n") + "\n".Length); // Let's clear empty spaces var start_index = 0; // Let's start from the beggining for (var i = 0; i < sub.Length; i++) { if (sub[i] == ' ') { continue; } start_index = i; break; } var end_index = sub.Length; // Starts from the end for (var j = sub.Length - 5; j >= 0; j--) // Removes the last <a href> tag { if (sub[j] == ' ') { continue; } end_index = j; break; } var team = sub.Substring(start_index, end_index - start_index + 1); teams.Add(team, url); } var _comp = db.Competitions.FirstOrDefault(c => c.Name == league); //TODO: Não preciso de estar sempre aqui Uma vez que a foreach (var team in teams.Keys) { // Inserir aqui as equipas na BD se não existirem if (!db.Teams.Any(x => x.Name == team)) // Todo: Alojar na memória só uma vez { db.Teams.InsertOnSubmit(new Team() { Name = team, compName = league, Country_name = "Portugal" }); db.SubmitChanges(); } else { var _team = db.Teams.First(x => x.Name == team); if (_team != null /* && team.compName == null*/) { if (_comp == null) { // TODO: Não existe a competição tenho de a criar.. var x = new Competition() { Name = league, //TODO: Adicionar os países e o nível da liga (1,2,3,4,5).. }; _comp = x; } _team.Competition1 = _comp; db.SubmitChanges(); } } doc = Webget.Load(teams[team]); var node = doc.DocumentNode.SelectNodes("//*[@id=\"s\"]/div/div/div/div/div[2]/div/div[1]/table/tbody"); var _cont = 0; foreach (var row in node.Elements()) { _cont = _cont + 1; if (_cont >= node.Elements().Count() / 2) { continue; } var data = row.SelectSingleNode( "/html[1]/body[1]/div[12]/div[2]/div[1]/div[1]/div[1]/div[1]/div[2]/div[1]/div[1]/table[1]/tbody[1]/tr[" + _cont + "]/td[1]").InnerText; var comp = row.SelectSingleNode( "/html[1]/body[1]/div[12]/div[2]/div[1]/div[1]/div[1]/div[1]/div[2]/div[1]/div[1]/table[1]/tbody[1]/tr[" + _cont + "]/td[3]/a").InnerText; if (!comp.Contains("Segunda Liga 17/18")) { continue; } var res = row.SelectSingleNode( "/html[1]/body[1]/div[12]/div[2]/div[1]/div[1]/div[1]/div[1]/div[2]/div[1]/div[1]/table[1]/tbody[1]/tr[" + _cont + "]/td[5]/a").InnerText; if (res.ToLower().Contains("vs") || res.ToLower().Contains("adiado") || res.ToLower().Contains("postponed")) { continue; } var away = row.SelectSingleNode( "/html[1]/body[1]/div[12]/div[2]/div[1]/div[1]/div[1]/div[1]/div[2]/div[1]/div[1]/table[1]/tbody[1]/tr[" + _cont + "]/td[6]/a").InnerText; var home = row.SelectSingleNode( "/html[1]/body[1]/div[12]/div[2]/div[1]/div[1]/div[1]/div[1]/div[2]/div[1]/div[1]/table[1]/tbody[1]/tr[" + _cont + "]/td[4]/a").InnerText; res = res.Replace("\r\n", ""); away = away.Replace("\r\n", ""); home = home.Replace("\r\n", ""); // Let's clear empty spaces var start_index = 0; // Let's start from the beggining for (var i = 0; i < res.Length; i++) { if (res[i] == ' ') { continue; } start_index = i; break; } var end_index = res.Length; // Starts from the end for (var j = res.Length - 5; j >= 0; j--) // Removes the last <a href> tag { if (res[j] == ' ') { continue; } end_index = j; break; } res = res.Substring(start_index, end_index - start_index + 1); start_index = 0; // Let's start from the beggining for (var i = 0; i < away.Length; i++) { if (away[i] == ' ') { continue; } start_index = i; break; } end_index = away.Length; // Starts from the end for (var j = away.Length - 5; j >= 0; j--) // Removes the last <a href> tag { if (away[j] == ' ') { continue; } end_index = j; break; } away = away.Substring(start_index, end_index - start_index + 1); start_index = 0; // Let's start from the beggining for (var i = 0; i < home.Length; i++) { if (home[i] == ' ') { continue; } start_index = i; break; } end_index = home.Length; // Starts from the end for (var j = home.Length - 5; j >= 0; j--) // Removes the last <a href> tag { if (home[j] == ' ') { continue; } end_index = j; break; } home = home.Substring(start_index, end_index - start_index + 1); var gamesOnDB = db.Matches.Where(c => c.Competition_name == league).ToList(); // Todo: Put generic var s = res.Split('-'); var h = Convert.ToInt32(s[0]); var a = Convert.ToInt32(s[1]); char resultado; if (h > a) { resultado = 'H'; } else if (a > h) { resultado = 'A'; } else { resultado = 'D'; } var game = new Match { Home_team = home, Away_team = away, away_goals = a, home_goals = h, date = Convert.ToDateTime(data), final_result = resultado, Competition_name = league //, //Team = db.Teams.Single(c => c.Country_name == country && c.Name == games[j].HomeTeam), //Team1 = db.Teams.Single(c => c.Country_name == country && c.Name == games[j].AwayTeam) }; if (GamesExistsDb(game, gamesOnDB)) { continue; } db.Matches.InsertOnSubmit(game); // Bug: Don't commit outside! If you commit outside it will bug the SubmitChanges() submitting already stuff on DB! db.SubmitChanges(); // Inserir aqui os dados na BD } } db.Dispose(); }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { var db = new soccerDBDataContext(); comboBox2.DataSource = db.Competitions.Where(x => x.Country_name == comboBox1.Text).Select(x => x.Name); }
public void readCSV(string path, string country, string league, int tipo) { //league += " 17/18"; var games = new List <GamesCSV>(); using (var parser = new TextFieldParser(path)) { parser.CommentTokens = new[] { "#" }; parser.SetDelimiters(new string[] { "," }); parser.HasFieldsEnclosedInQuotes = false; parser.ReadLine(); // Skip first line while (!parser.EndOfData) { var a = parser.ReadFields(); games.Add(new GamesCSV(a)); } } // TODO: We have to make sure the country already exists in the DB //var teams = games.Select(p => p.HomeTeam).Distinct().Select(t => new Team {name = t, country = "Itália"}); // Vai buscar as equipas ao ficheiro CSV Italiano var teams = games.Select(p => p.HomeTeam).Distinct(); var db = new soccerDBDataContext(); var teamdb = db.Teams.Where(c => c.Country_name == country).Select(n => n.Name); // I only want team names var comp = db.Competitions.FirstOrDefault(c => c.Name == league); //TODO: Não preciso de estar sempre aqui Uma vez que a // Todo: Make a new algorithm for the team names (see if it already exists) SOLUTION: ADD ALTERNATIVE NAMES foreach (var t in teams) // Iterate each team on .csv { var similarity = 0.0; string f = null; if (teamdb.Contains(t)) { var team = db.Teams.FirstOrDefault(c => c.Country_name == country && c.Name == t); if (team != null /* && team.compName == null*/) { if (comp == null) { // TODO: Não existe a competição tenho de a criar.. var x = new Competition() { Name = league, //TODO: Adicionar os países e o nível da liga (1,2,3,4,5).. }; comp = x; } team.Competition1 = comp; db.SubmitChanges(); } } else { var te = new Team { Name = t, Country_name = country, compName = league }; db.Teams.InsertOnSubmit(te); // TODO: SEE IF THE TEAM ALREADY EXISTS IN DB PROPERLY //db.SubmitChanges(); } /*foreach (var s in teamdb) // Iterate each team on DB * { * var sim = StringCompare(s, t); * if (sim > similarity) * { * similarity = sim; * f = s; * } * } * * if (f != null && similarity > 0.7 && similarity != 1) // Let's put similarity at 43% * { * foreach (var d in games) * { * if (d.HomeTeam == t) * { * d.HomeTeam = f; * } * else if (d.AwayTeam == t) * { * d.AwayTeam = f; * } * } * } * else if (similarity <= 0.8) * { * if (!db.Competitions.Any(c => c.Name == league)) // The league doesn't exist * { * var comp = new Competition() * { * Name = league, * Country_name = country, * Type = tipo * }; * * db.Competitions.InsertOnSubmit(comp); * * db.SubmitChanges(); * } * * var te = new Team * { * Name = t, * Country_name = country, * compName = league * }; * * db.Teams.InsertOnSubmit(te); * * //teamsToAdd.Add(new Team() { Name = t, Country_name = country, Competition = new Competition() {Name = league} }); // Adds to the list to add * }*/ db.SubmitChanges(); } var gamesOnDB = db.Matches.Where(c => c.Competition_name == league).ToList(); for (var j = 0; j < games.Count(); j++) // TODO: Check if match is already on the database { var game = new Match { Home_team = games[j].HomeTeam, Away_team = games[j].AwayTeam, away_goals = games[j].AwayScore, home_goals = games[j].HomeScore, date = games[j].Date, final_result = games[j].FinalScore, Competition_name = league //, //Team = db.Teams.Single(c => c.Country_name == country && c.Name == games[j].HomeTeam), //Team1 = db.Teams.Single(c => c.Country_name == country && c.Name == games[j].AwayTeam) }; if (GamesExistsDb(game, gamesOnDB)) { continue; // TODO: Caution! I've added teams from different leagues in a match! } db.Matches.InsertOnSubmit(game); // Bug: Don't commit outside! If you commit outside it will bug the SubmitChanges() submitting already stuff on DB! db.SubmitChanges(); } db.Dispose(); MessageBox.Show("Added games!"); }
public void populate2(string league, string startDate, string endDate) // TODO: Today date { var calc = new Calculator(); var db = new soccerDBDataContext(); int best1 = 0; int best2 = 0; double final = 0.0; int cont_win_lose = 0; int over_under = 0; int cont = 0; int numTeams = 18; int numJogos = numTeams / 2; var xasd = new Dictionary <Match, List <Calculator.ProbabilityOdds> >(); for (int i = 0; i < 5; i++) // 5 jornadas { var matches = db.Matches.Where(x => x.Competition_name == league).OrderByDescending(x => x.date).Take((i + 1) * numJogos); //var poisson = new List<Calculator.ProbabilityOdds>(); // best = 1.13 com 90 jogos (2ª LIGA) //best = 1.1 com 99 jogos (1ª LIGA) [43, 72] / 99 || POISSON COM 90 JOGOS CHEGA A 71% for (var theta = 0.3; theta < 0.7; theta += 0.1) { foreach (var match in matches) { var d = calc.CalculateGoalExpectancy(match.Home_team, match.Away_team, league, startDate, Convert.ToString(match.date, null).Split()[0]); if (d.homeGoalExpectancy <= 0.2) { d.homeGoalExpectancy = 0.5; } if (d.awayGoalExpectancy <= 0.2) { d.awayGoalExpectancy = 0.5; } //var p = calc.ReturnPoissonProbScores(d.homeGoalExpectancy, d.awayGoalExpectancy); // 40, 57 var p = calc.MaxWell(d.homeGoalExpectancy, d.awayGoalExpectancy, theta); // 246, 316 var maxwell = (new Calculator.ProbabilityOdds() { Away = p.Away, awayGoalExpectancy = d.awayGoalExpectancy, Draw = p.Draw, Home = p.Home, homeGoalExpectancy = d.homeGoalExpectancy, Over25 = p.Over25, Under25 = p.Under25, ThetaMaxWell = theta, numJornadas = i + 1, over_under = 0, cont_win_lose = 0 }); if (xasd.ContainsKey(match)) // A partida já existe com outras odds { xasd[match].Add(maxwell); } else // Ainda não foi calculado.. Inserir o 1º elemento { xasd.Add(match, new List <Calculator.ProbabilityOdds> { maxwell }); } } } } foreach (var match in xasd.Keys) // Vou ver em quantos jogos acertei.. { var odds = xasd[match]; foreach (var p in odds) { if (p.Home > 40 && match.final_result == 'H') // TODO: Ter atenção a distribuição de probabilidades.. { p.cont_win_lose++; } if (p.Away > 40 && match.final_result == 'A') { p.cont_win_lose++; } if (p.Under25 > 50 && match.away_goals + match.home_goals < 2.5) { p.over_under++; } if (p.Over25 > 50 && match.away_goals + match.home_goals > 2.5) { p.over_under++; } } } foreach (var match in xasd.Keys) { var odds = xasd[match]; var group = odds.GroupBy(x => x.numJornadas).ToList(); foreach (var t in group) { var f = t.OrderByDescending(x => x.cont_win_lose + x.over_under).Take(1).Select(x => x.ThetaMaxWell); double bart = f.First(); var d = odds.Where(p => p.ThetaMaxWell == bart && p.cont_win_lose + p.over_under >= 1); } } }
public void testTheta(string league, string startDate, string endDate, double theta) { var calc = new Calculator(); var db = new soccerDBDataContext(); int numTeams = db.Teams.Count(x => x.Competition1.Name == league); int numJogos = numTeams / 2; int numJornadas = 6; int aw = 0; int hw = 0; int under = 0; int over = 0; int i = numJornadas - 1; //var vals = new Dictionary<double, int>(); //for (int i = 0; i < 5; i++) // 5 jornadas //{ var matches = db.Matches.Where(x => x.Competition_name == league).OrderByDescending(x => x.date).Take((i + 1) * numJogos); foreach (var match in matches) { var d = calc.CalculateGoalExpectancy(match.Home_team, match.Away_team, league, startDate, Convert.ToString(match.date, null).Split()[0]); if (d.homeGoalExpectancy <= 0.2) { d.homeGoalExpectancy = 0.5; } if (d.awayGoalExpectancy <= 0.2) { d.awayGoalExpectancy = 0.5; } //var p = calc.ReturnPoissonProbScores(d.homeGoalExpectancy, d.awayGoalExpectancy); // 40, 57 var p = calc.MaxWell(d.homeGoalExpectancy, d.awayGoalExpectancy, theta); // 246, 316 var maxwell = (new Calculator.ProbabilityOdds() { Away = p.Away, awayGoalExpectancy = d.awayGoalExpectancy, Draw = p.Draw, Home = p.Home, homeGoalExpectancy = d.homeGoalExpectancy, Over25 = p.Over25, Under25 = p.Under25, ThetaMaxWell = theta, numJornadas = 5, over_under = 0, cont_win_lose = 0 }); if (p.Home > 40 && match.final_result == 'H') { hw++; } if (p.Away > 40 && match.final_result == 'A') { aw++; } if (p.Under25 > 50 && match.away_goals + match.home_goals < 2.5) { under++; } if (p.Over25 > 50 && match.away_goals + match.home_goals > 2.5) { over++; } //} } MessageBox.Show(numJogos * numJornadas + "\n\nHome Win: " + hw + "\nAWAY WIN: " + aw + "\nUNDER: " + under + "\nOVER: " + over); }
public ProbabilityOdds CalculateGoalExpectancy(string home, string away, string league, string startDate, string endDate) { var db = new soccerDBDataContext(); double?output = null; // TODO: Ver esta situação da data no store procedure da DB dd-mm-yyyy // TODO: Ver as equipas da nova liga que foram adicionadas.. Para não haver conflitos com épocas passadas var _avg = db.AVG_GOALS_GAME(league, endDate, startDate, ref output); // % total de golos por jogo (Marcados e sofridos) foreach (var s in _avg) { output = s.Column1; } double?away_favor = 0; double?away_against = 0; var avg_away = db.AVG_AWAY_TEAM(league, away, endDate, startDate); // % GOLOS MARCADOS / SOFRIDOS FORA (POR EQUIPA) foreach (var s in avg_away) { away_favor = s.__MARCADOS; away_against = s.__SOFRIDOS; } double?home_favor = 0; double?home_against = 0; var avg_home = db.AVG_HOME_TEAM(league, home, endDate, startDate); // % GOLOS MARCADOS / SOFRIDOS CASA (POR EQUIPA)) foreach (var s in avg_home) { home_favor = s.__MARCADOS; home_against = s.__SOFRIDOS; } double?home_avg_favor = 0; double?home_avg_against = 0; var avg_home_league = db.AVG_HOME_GOALS(league, endDate, startDate); foreach (var s in avg_home_league) { home_avg_favor = s.Média_golos_marcados_casa; home_avg_against = s.Média_golos_sofridos_casa; } double?away_avg_favor = 0; double?away_avg_against = 0; var away_home_league = db.AVG_AWAY_GOALS(league, startDate, endDate); foreach (var s in away_home_league) { away_avg_favor = s.Média_golos_marcados_fora; away_avg_against = s.Média_golos_sofridos_fora; } /*MessageBox.Show("--HOME--\n\n" + home_favor + "\n" + home_against + "\n\n--AWAY--\n\n" + away_favor + "\n" + * away_against + "\n\n--LEAGUE HOME--\n\n" + home_avg_favor + "\n" + home_avg_against);*/ var tye = db.POWER_HOME(league, home, endDate, startDate); // % total de golos por jogo (Marcados e sofridos) double?home_att = 0; double?home_def = 0; foreach (var x in tye) { home_att = x.PODER_ATAQUE; home_def = x.PODER_DEFENSIVO; } var rxs = db.POWER_AWAY(league, away, startDate, endDate); double?away_att = 0; double?away_def = 0; foreach (var x in rxs) { away_att = x.PODER_ATAQUE; away_def = x.PODER_DEFENSIVO; } // TODO: To prevent 0 theta in poisson (it means they are 1% better or worst then average) if (home_att == 0.0) { home_att = 0.1; } if (away_def == 0.0) { away_def = 0.1; } if (away_att == 0.0) { away_att = 0.1; } if (home_def == 0.0) { home_def = 0.1; } var homeTeamGoalExpectancy = home_att * away_def * home_avg_favor; var awayTeamGoalExpectancy = away_att * home_def * away_avg_favor; //MessageBox.Show("Calculated!\n" + homeTeamGoalExpectancy + "\n" + awayTeamGoalExpectancy); //CalculatePoissonProbScores(homeTeamGoalExpectancy, awayTeamGoalExpectancy); //var scores = ReturnPoissonProbScores(homeTeamGoalExpectancy, awayTeamGoalExpectancy); return(new ProbabilityOdds() { homeGoalExpectancy = homeTeamGoalExpectancy, awayGoalExpectancy = awayTeamGoalExpectancy }); }