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(); }
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!"); }