public void InsertCountry(string pCountry, string pLink)
{
using (IDbConnection conn = data.Connection())
{
var value = conn.Query <bool>("sp_InsertCounty",
new {
Country = pCountry,
Link = pLink
}, commandType: CommandType.StoredProcedure);
Debug.WriteLine("Country at " + DateTime.Now + " Result: " + value.ToString());
}
//using (SampleDataDataContext DbData = new SampleDataDataContext())
//{
// COUNTRY country = DbData.COUNTRies.SingleOrDefault(x => x.country_Name == pCountry);
// if (country == null)
// {
// try
// {
// COUNTRY newCountry = new COUNTRY()
// {
// country_Name = pCountry,
// link = pLink
// };
// DbData.COUNTRies.InsertOnSubmit(newCountry);
// DbData.SubmitChanges();
// }
// catch (Exception sql)
// {
// MessageBox.Show("An error occurred...\n" + sql.Message);
// }
// }
//}
}
public List <Team> GetRegularScorer(int pGamesToCount)
{
using (IDbConnection conn = dor.Connection())
{
IEnumerable <Team> team = conn.Query <Team>(@"SELECT * FROM [dbo].[TEAM]");
//Go Through all the teams in the TEAMs table
foreach (Team selectedTeam in team)
{
int totalHomeGamesPlayed = 0;
int countScore = 0;
string teamName = selectedTeam.nameOfTeam;
string competition = selectedTeam.currentCompetition;
string country = selectedTeam.country;
int totalGamesToCount = pGamesToCount;
int countWins = 0;
//Team must atleast have played 5 games
const int TOTALNUMBER = 5;
//Retrieve all the results from the stored procedure
var evResults = conn.Query <Results>(@"SELECT * FROM [dbo].[RESULTS] WHERE [homeTeam]=@teamName OR [awayTeam]=@teamName
[country]=@country AND [competition]=@competition",
new
{
teamName = teamName,
country = country,
competition = competition
}).ToList();
//List<Results> evResults = DbData.RESULTs.Where(r => ((r.homeTeam.Equals(teamName) || r.awayTeam.Equals(teamName))
// && (r.country.Equals(country) && r.competition.Equals(competition))))
// .Select(x => (x)).ToList();
//Get how many of those games the team scored
countScore = evResults.FindAll(x => (x.HomeTeamScore) > 0 || (x.AwayTeamScore > 0)).Count;
if (countScore >= totalGamesToCount && selectedTeam.played > TOTALNUMBER)
{
//MessageBox.Show("Total Home Games: " + totalHomeGamesPlayed + "\nTotal Home Wins: " + countHomeWins + "\n" + selectedTeam.nameOfTeam);
regularScorer.Add(selectedTeam);
}
}
}
return(regularScorer);
}
//private int groupLoop = 5;
//public int GroupLoop
//{
// get
// {
// return groupLoop;
// }
// set
// {
// groupLoop = value;
// }
//}
//public enum ResultState { Draw, HomeWin, AwayWin, Posponed }
#endregion
#region Methods
public void InsertResults(string pHomeTeam, string pAwayTeam, DateTime pDate, string pCountry,
string pCOMPETITION, string pHomeScore, string pAwayScore, string state)
{
Data_Organiser data = new Data_Organiser();
using (IDbConnection conn = data.Connection())
{
var value = conn.Query <bool>("sp_InsertResults",
new
{
Country = pCountry,
Competition = pCOMPETITION,
DateOfmatch = pDate,
HomeTeam = pHomeTeam,
AwayTeam = pAwayTeam,
HomeTeamScore = pHomeScore,
AwayTeamScore = pAwayScore,
MatchState = state
}, commandType: CommandType.StoredProcedure).ToList();
Debug.WriteLine("Match RESULTS at " + DateTime.Now + " Result: " + value[0].ToString().ToUpper());
}
//using (SampleDataDataContext DbData = new SampleDataDataContext())
//{
// RESULT result = DbData.RESULTs.SingleOrDefault(x => (x.homeTeam == pHomeTeam) && (x.awayTeam == pAwayTeam)
// && (x.dateOfmatch == pDate));
// if (result == null)
// {
// try
// {
// RESULT newResult = new RESULT()
// {
// country = pCountry,
// competition = pCOMPETITION,
// dateOfmatch = pDate,
// timeOfMatch = "unavailable",
// homeTeam = pHomeTeam,
// awayTeam = pAwayTeam,
// homeTeamScore = pHomeScore,
// awayTeamScore = pAwayScore,
// matchState = state
// };
// DbData.RESULTs.InsertOnSubmit(newResult);
// DbData.SubmitChanges();
// }
// catch (Exception sql)
// {
// MessageBox.Show("An error occurred while inserting results..\n" + sql.Message);
// }
// }
//}
}
//public GamePlay(string pHomePrediction, string pAwayPrediction, string pDate, double pHomeWins, double pHomeLosses, double pHomeDraws,
// double pAwayWins, double pAwayLosses, double pAwayDraws)
//{
// this.HomePrediction = pHomePrediction;
// this.AwayPrediction = pAwayPrediction;
// this.Date = pDate;
// this.HomeWins = pHomeWins;
// this.HomeLosses = pHomeLosses;
// this.HomeDraws = pHomeDraws;
// this.AwayWins = pAwayWins;
// this.AwayLosses = pAwayLosses;
// this.AwayDraws = pAwayDraws;
//}
#endregion
public virtual List <GamePlay> GetFixture(DateTime pDate)
{
using (IDbConnection conn = data.Connection())
{
IEnumerable <Fixture> fixture = conn.Query <Fixture>("sp_GetFixture",
new { DateOfmatch = pDate }, commandType: CommandType.StoredProcedure).ToList();
//IEnumerable<Fixture> fixture = DbData.FIXTUREs
// .Where(game => (game.dateOfmatch.Equals(pDate) && (!game.competition.Contains("copa")
// && !game.competition.Contains("cup"))))
// .OrderByDescending(team => (team.timeOfMatch)).Select(team => (team));
//Go through each fixture
foreach (Fixture match in fixture)
{
//Copy the team's form statement
player.Add(GetTeamsForm(match));
player.Remove(null);
}
string test = "";
return(player);
}
}
public void InsertFixture(string pCountry, string pCompetition, string pHomeTeam, string pAwayTeam, DateTime pDate, string pTime)
{
Data_Organiser data = new Data_Organiser();
using (IDbConnection conn = data.Connection())
{
var value = conn.Query <bool>("sp_InsertFixture",
new
{
Country = pCountry,
Competition = pCompetition,
HomeTeam = pHomeTeam,
AwayTeam = pAwayTeam,
DateOfmatch = pDate,
TimeOfMatch = pTime,
FixtureState = "NORMAL FIXTURE"
}, commandType: CommandType.StoredProcedure).ToList();
Debug.WriteLine("Match FIXTURE at " + DateTime.Now + " Result: " + value[0].ToString().ToUpper());
}
//using (SampleDataDataContext DbData = new SampleDataDataContext())
//{
// FIXTURE fixture = DbData.FIXTUREs.SingleOrDefault(x => (x.homeTeam == pHomeTeam) && (x.awayTeam == pAwayTeam) && (x.dateOfmatch == pDate));
// if (fixture == null)
// {
// try
// {
// FIXTURE newFixture = new FIXTURE()
// {
// country = pCountry,
// competition = pCompetition,
// homeTeam = pHomeTeam,
// awayTeam = pAwayTeam,
// dateOfmatch = pDate,
// timeOfMatch = pTime,
// fixtureState = "NORMAL FIXTURE"
// };
// DbData.FIXTUREs.InsertOnSubmit(newFixture);
// DbData.SubmitChanges();
// }
// catch (Exception sql)
// {
// MessageBox.Show("An error occurred while inserting fixture...\n" + sql.Message);
// }
// }
//}
}
//protected int NumberOfTeams { get; set; }
#endregion
#region Methods
public void InsertCompetition(string pCompetition, string pCountry, string pLink)
{
Data_Organiser data = new Data_Organiser();
using (IDbConnection conn = data.Connection())
{
var retval = conn.Query <bool>("[dbo].[sp_InsertCompetition]",
new
{
Competition = pCompetition,
Country = pCountry,
Link = pLink
}, commandType: CommandType.StoredProcedure);
Debug.WriteLine("COMPETITION at " + DateTime.Now + " Result: " + retval.ToString().ToUpper());
}
//using (SampleDataDataContext DbData = new SampleDataDataContext())
//{
// COMPETITION league = DbData.COMPETITIONs.SingleOrDefault(x => (x.titleOfCompetition == pCompetition));
// if (league == null)
// {
// try
// {
// COMPETITION newCompetition = new COMPETITION()
// {
// titleOfCompetition = pCompetition,
// country = pCountry,
// link = pLink
// };
// DbData.COMPETITIONs.InsertOnSubmit(newCompetition);
// DbData.SubmitChanges();
// }
// catch (Exception sql)
// {
// MessageBox.Show("An error occurred while inserting league..\n" + sql.Message);
// }
// }
//}
}
public void InsertTeamsForm(string pCountry, string pCompetition, string teamName, string pLink
, char num1, char num2, char num3, char num4, char num5)
{
Data_Organiser data = new Data_Organiser();
using (IDbConnection conn = data.Connection())
{
var retval = conn.Query <bool>("sp_InsertTeamsForm",
new
{
nameOfTeam = teamName,
country = pCountry,
competition = pCompetition,
competitionLink = pLink,
_1 = num1,
_2 = num2,
_3 = num3,
_4 = num4,
_5 = num5
}, commandType: CommandType.StoredProcedure).ToList();
Debug.WriteLine("TEAMS FORM at " + DateTime.Now + " Result: " + retval[0].ToString().ToUpper());
//TEAMSFORM team = DbData.TEAMSFORMs.SingleOrDefault(t => (t.country.Equals(pCountry) && (t.competition.Equals(pCompetition)
// && (t.nameOfTeam.Equals(teamName)))));
//if (team == null)
//{
// try
// {
// TEAMSFORM teamsForm = new TEAMSFORM()
// {
// nameOfTeam = teamName,
// country = pCountry,
// competition = pCompetition,
// competitionLink = pLink,
// _1 = num1,
// _2 = num2,
// _3 = num3,
// _4 = num4,
// _5 = num5
// };
// DbData.TEAMSFORMs.InsertOnSubmit(teamsForm);
// DbData.SubmitChanges();
// }
// catch (Exception ex)
// {
// MessageBox.Show("An error occurred while inserting teams form..." + ex.Message);
// }
//}
//else
//{
// try
// {
// team._1 = num1;
// team._2 = num2;
// team._3 = num3;
// team._4 = num4;
// team._5 = num5;
// DbData.SubmitChanges();
// }
// catch (Exception ex)
// {
// MessageBox.Show("An error occurred while updating teams form..." + ex.Message);
// }
//}
}
}
//Data_Organiser data = new Data_Organiser();
public void InsertRecalledMatch(string pCountry, string pCompetition, DateTime pDate,
string pHomeTeam, string pAwayTeam, string pMatchState)
{
Data_Organiser data = new Data_Organiser();
using (IDbConnection conn = data.Connection())
{
var value = conn.Query <bool>("sp_InsertRecalledMatch",
new
{
Country = pCountry,
Competition = pCompetition,
DateOfmatch = pDate,
HomeTeam = pHomeTeam,
AwayTeam = pAwayTeam,
MatchState = pMatchState
}, commandType: CommandType.StoredProcedure);
Debug.WriteLine("Match RECALLED at " + DateTime.Now + " Result: " + value.ToString().ToUpper());
}
//using (SampleDataDataContext DbData = new SampleDataDataContext())
//{
// RECALLEDMATCH match = DbData.RECALLEDMATCHes.SingleOrDefault(x => (x.homeTeam.Equals(pHomeTeam)) && (x.awayTeam.Equals(pAwayTeam))
// && (x.dateOfmatch.Equals(pDate)) && (x.country.Equals(pCountry))
// && (x.competition.Equals(pCompetition)));
// RECALLEDMATCH newRematch = new RECALLEDMATCH()
// {
// country = pCountry,
// competition = pCompetition,
// dateOfmatch = pDate,
// homeTeam = pHomeTeam,
// awayTeam = pAwayTeam
// };
// if (match == null)
// {
// try
// {
// RECALLEDMATCH recalledMatch = new RECALLEDMATCH()
// {
// country = pCountry,
// competition = pCompetition,
// dateOfmatch = pDate,
// homeTeam = pHomeTeam,
// awayTeam = pAwayTeam,
// matchState = pMatchState
// };
// DbData.RECALLEDMATCHes.InsertOnSubmit(recalledMatch);
// DbData.SubmitChanges();
// }
// catch (Exception ex)
// {
// MessageBox.Show("An error occurred while inserting into RecalledMatch...\n" + ex);
// }
// }
// else
// {
// string continueString = "";
// }
//}
}
//Populate the log standings in each Competition
public void PopulateLog(string pTeamName, string pCountry, string pCompetition, string pLink,
int pW, int pL, int pD, int pPos, int pMP, int pGA, int pGF, string pGD, int pPoints)
{
using (IDbConnection conn = data.Connection())
{
var retval = conn.Query <string>("sp_PopulateLog",
new
{
country = pCountry,
currentCompetition = pCompetition,
nameOfTeam = pTeamName,
wins = pW,
draws = pD,
lost = pL,
position = pPos,
points = pPoints,
played = pMP,
goalAgainst = pGA,
goalForward = pGF,
goalDifference = pGD,
link = pLink
}, commandType: CommandType.StoredProcedure).ToList();
Debug.WriteLine("TEAM at " + DateTime.Now + " Result: " + retval[0].ToString().ToUpper());
}
// using (SampleDataDataContext DbData = new SampleDataDataContext())
// {
// Team getRow = DbData.TEAMs.SingleOrDefault(x => ((x.country == pCountry) &&
// (x.currentCompetition == pCompetition)) && (x.nameOfTeam == pTeamName));
// Team newTeam = new Team
// {
// country = pCountry,
// currentCompetition = pCompetition,
// nameOfTeam = pTeamName,
// wins = pW,
// draws = pD,
// lost = pL,
// position = pPos,
// points = pPoints,
// played = pMP,
// goalAgainst = pGA,
// goalForward = pGF,
// goalDifference = pGD,
// link = pLink
// };
// if (getRow == null)
// {
// try
// {
// Team newHomeTeam = new Team()
// {
// country = pCountry,
// currentCompetition = pCompetition,
// nameOfTeam = pTeamName,
// wins = pW,
// draws = pD,
// lost = pL,
// position = pPos,
// points = pPoints,
// played = pMP,
// goalAgainst = pGA,
// goalForward = pGF,
// goalDifference = pGD,
// link = pLink
// };
// DbData.TEAMs.InsertOnSubmit(newHomeTeam);
// DbData.SubmitChanges();
// }
// catch (Exception ex)
// {
// MessageBox.Show("An error occurred while inserting team..\n" + ex.Message);
// }
// }
// else if (!getRow.Equals(newTeam))
// {
// try
// {
// getRow.link = pLink;
// getRow.wins = pW;
// getRow.draws = pD;
// getRow.lost = pL;
// getRow.position = pPos;
// getRow.points = pPoints;
// getRow.played = pMP;
// getRow.goalAgainst = pGA;
// getRow.goalForward = pGF;
// getRow.goalDifference = pGD;
// DbData.SubmitChanges();
// }
// catch (Exception ex)
// {
// MessageBox.Show("An error occurred while update log team...\n" + ex.Message);
// }
// }
// }
}
private Tuple <char?, Color> GetFormValueSymbol(int pFormColumn, string pTeamName, string pCountry, string pCompetition)
{
TeamsForm homeTeamForm;
TeamsForm awayTeamForm;
char? r1 = null;//, r2, r3, r4, r5;
Color symbolColor = Color.White;
using (IDbConnection conn = data.Connection())
{
//Tuple<char, char> name;
//new Tuple<char, char>('s', 'n');
//object obj = new { Name = pCountry };
homeTeamForm = conn.Query <TeamsForm>(@"SELECT * FROM [dbo].[TEAMSFORM] WHERE [nameOfTeam]=@TeamName AND
[competition]=@Competition AND [country]=@Country",
new
{
TeamName = pTeamName,
Competition = pCompetition,
Country = pCountry
}).FirstOrDefault();
awayTeamForm = conn.Query <TeamsForm>(@"SELECT * FROM [dbo].[TEAMSFORM] WHERE [nameOfTeam]=@TeamName AND
[competition]=@Competition AND [country]=@Country",
new
{
TeamName = pTeamName,
Competition = pCompetition,
Country = pCountry
}).FirstOrDefault();
//homeTeamForm = DbData.TEAMSFORMs.SingleOrDefault(t => (t.nameOfTeam.Equals(pTeamName)
// && (t.competition.Equals(pCompetition)) && (t.country.Equals(pCountry))));
//awayTeamForm = DbData.TEAMSFORMs.SingleOrDefault(t => (t.nameOfTeam.Equals(pTeamName)
// && (t.competition.Equals(pCompetition)) && (t.country.Equals(pCountry))));
}
if (pTeamName == homeTeamForm.nameOfTeam || homeTeamForm != null)
{
switch (pFormColumn)
{
case 1: r1 = homeTeamForm._1;
symbolColor = GetColor(r1);
break;
case 2: r1 = homeTeamForm._2;
symbolColor = GetColor(r1);
break;
case 3: r1 = homeTeamForm._3;
symbolColor = GetColor(r1);
break;
case 4: r1 = homeTeamForm._4;
symbolColor = GetColor(r1);
break;
case 5: r1 = homeTeamForm._5;
symbolColor = GetColor(r1);
break;
default: MessageBox.Show("Invalid input");
break;
}
}
else
{
switch (pFormColumn)
{
case 1: r1 = awayTeamForm._1;
symbolColor = GetColor(r1);
break;
case 2: r1 = awayTeamForm._2;
symbolColor = GetColor(r1);
break;
case 3: r1 = awayTeamForm._3;
symbolColor = GetColor(r1);
break;
case 4: r1 = awayTeamForm._4;
symbolColor = GetColor(r1);
break;
case 5: r1 = awayTeamForm._5;
symbolColor = GetColor(r1);
break;
default: MessageBox.Show("Invalid input");
break;
}
}
return(new Tuple <char?, Color>(r1, symbolColor));
}
public async void FullUpdateOfResults()
{
int countTimeout = 0;
RecalledMatch reviewedMatch = new RecalledMatch();
Data_Organiser dor = new Data_Organiser();
Fixture fixture = new Fixture();
using (IDbConnection conn = dor.Connection())
{
try
{
var links = conn.Query <Team>(" SELECT * FROM [Football].[dbo].[TEAM]");
foreach (Team linksItem in links)
{
HttpClient httpClient = new HttpClient();
String html = await httpClient.GetStringAsync(constantUrl + linksItem.link);
HtmlAgilityPack.HtmlDocument htmlDocument = new HtmlAgilityPack.HtmlDocument();
htmlDocument.LoadHtml(html);
List <HtmlNode> MatchHtml = htmlDocument.DocumentNode.Descendants("tr")
.Where(node => (node.GetAttributeValue("id", "")
.Contains("page_team_1_block_team_matches"))).ToList();
foreach (HtmlNode MatchListItem in MatchHtml)
{
string date = GetMatchData(MatchListItem).Item1;
DateTime evaluatedDate = Convert.ToDateTime(date);
string homeTeam = GetMatchData(MatchListItem).Item2;
string awayTeam = GetMatchData(MatchListItem).Item3;
string score = GetMatchData(MatchListItem).Item4;
string homeScore = "";
string awayScore = "";
ResultState state = ResultState.UNDEFINED;
OptionState optionState = OptionState.Result;
string time = "";
if (dor.IsSubstringable(score, ref homeScore, ref awayScore) && score.Contains(':'))
{
optionState = OptionState.Fixture;
time = score;
//dor.SlicingData(linksItem.country, linksItem.currentCompetition, evaluatedDate, homeTeam, awayTeam,
// homeScore, awayScore, OptionState.Fixture.ToString(), score);
fixture.InsertFixture(linksItem.country, linksItem.currentCompetition, homeTeam, awayTeam, evaluatedDate, score);
}
else if (dor.IsSubstringable(score, ref homeScore, ref awayScore) && score.Contains('P') || score.Contains('E') || score.Contains("PSTP"))
{
state = ResultState.POSTPONED;
reviewedMatch.InsertRecalledMatch(linksItem.country, linksItem.currentCompetition, evaluatedDate,
homeTeam, awayTeam, state.ToString());
continue;
}
else if (dor.IsSubstringable(score, ref homeScore, ref awayScore) && score == "CANC")
{
state = ResultState.CANCELLED;
reviewedMatch.InsertRecalledMatch(linksItem.country, linksItem.currentCompetition, evaluatedDate,
homeTeam, awayTeam, state.ToString());
continue;
}
else if (dor.IsSubstringable(score, ref homeScore, ref awayScore) && dor.IsNumber(homeScore, awayScore))
{
optionState = OptionState.Result;
dor.SlicingData(linksItem.country, linksItem.currentCompetition, evaluatedDate, homeTeam, awayTeam,
homeScore, awayScore, OptionState.Result.ToString(), time);
}
else
{
state = ResultState.UNDEFINED;
reviewedMatch.InsertRecalledMatch(linksItem.country, linksItem.currentCompetition, evaluatedDate,
homeTeam, awayTeam, state.ToString());
continue;
}
}
}
}
catch (TaskCanceledException tex)
{
Debug.WriteLine("\n" + tex.Message + " " + DateTime.Now + "\n");
FullUpdateOfResults();
//countTimeout++;
//if (countTimeout >= 5)
//{
//}
}
catch (Exception ex)
{
Debug.WriteLine("\n" + ex.Message + " " + DateTime.Now + "\n");
//FullUpdateOfResults();
//reviewedMatch.InsertRecalledMatch("COUNTRY", "COMPETITION", DateTime.Now,
// "HOME", "AWAY", "UNDEFINED");
}
}
}