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