private void btnClearPrediction_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Are you sure you want to clear your prediction?", "Clear Predictions", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
{
// Clear predections
int user_id = int.Parse(user[0]);
string query = String.Format("DELETE FROM TblPredictions WHERE User_id = '{0}'", user[0]);
using (SqlCommand cmd = new SqlCommand(query, dbh.GetCon()))
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
int tblPredictionResult = cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
if (tblPredictionResult > 0)
{
MessageHandler.ShowMessage(String.Format("You have deleted {0} results.", tblPredictionResult), "Result", MessageBoxButtons.OK, MessageBoxIcon.None);
}
else
{
MessageHandler.ShowMessage(String.Format("You didn't delete any rows."), "Result", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
// Update DB
}
}
private void insertPredictions(string un)
{
int numberOfGoalsHome = Convert.ToInt32(this.numberOfGoalsHome.Text);
int numberOfGoalsAway = Convert.ToInt32(this.numberOfGoalsAway.Text);
dbh.TestConnection();
dbh.OpenConnectionToDB();
int Id;
using (SqlCommand cmd = new SqlCommand("SELECT Id FROM [TblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("@Username", un);
Id = (int)cmd.ExecuteScalar();
}
int Points;
using (SqlCommand cmd = new SqlCommand("SELECT Points FROM [TblUsers] WHERE Id = @Id", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Id", Id);
Points = (int)cmd.ExecuteScalar();
}
int Game_id = Convert.ToInt32(game_id.Text);
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblPredictions ( User_id, Game_id, PredictedHomeScore, PredictedAwayScore) VALUES (@user_id, @Game_id, @PredictedHomeScore, @PredictedAwayScore)"))
{
cmd.Parameters.AddWithValue("@user_id", Id);
cmd.Parameters.AddWithValue("@Game_id", Game_id);
cmd.Parameters.AddWithValue("PredictedHomeScore", numberOfGoalsHome);
cmd.Parameters.AddWithValue("PredictedAwayScore", numberOfGoalsAway);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
dbh.CloseConnectionToDB();
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (txtPath.Text.Contains("Matches.csv"))
{
//Show wait cursor
Cursor = Cursors.WaitCursor;
if (!(txtPath.Text == null))
{
//Open db connection
dbh.OpenConnectionToDB();
DataTable importData = GetDataFromCsv();
if (importData != null)
{
//Call save import data from db handler
dbh.SaveImportDataToDatabase(importData);
MessageBox.Show("Import Games Completed");
//Show normal cursor
Cursor = Cursors.Default;
}
dbh.CloseConnectionToDB();
}
}
if (txtPath.Text.Contains("Teams.csv"))
{
//Show wait cursor
Cursor = Cursors.WaitCursor;
if (!(txtPath.Text == null))
{
//Open db connection
dbh.OpenConnectionToDB();
DataTable importData = GetDataFromCsv();
if (importData != null)
{
//Call save import data from db handler
dbh.SaveImportDataToDatabase(importData, "Teams");
MessageBox.Show("Import Teams Completed");
//Show normal cursor
Cursor = Cursors.Default;
}
dbh.CloseConnectionToDB();
}
}
if (txtPath.Text == "")
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void ShowResults()
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
DataTable hometable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.HomeTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.HomeTeam = tblTeams.Team_ID");
DataTable awayTable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.AwayTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.AwayTeam = tblTeams.Team_ID");
dbh.CloseConnectionToDB();
for (int i = 0; i < hometable.Rows.Count; i++)
{
DataRow dataRowHome = hometable.Rows[i];
DataRow dataRowAway = awayTable.Rows[i];
ListViewItem lstItem = new ListViewItem(dataRowHome["TeamName"].ToString());
lstItem.SubItems.Add(dataRowHome["HomeTeamScore"].ToString());
lstItem.SubItems.Add(dataRowAway["AwayTeamScore"].ToString());
if (Int32.Parse(dataRowAway["AwayTeamScore"].ToString()) > 0 | Int32.Parse(dataRowHome["HomeTeamScore"].ToString()) > 0)
{
CheckPrediction(dataRowHome["Teamname"].ToString(), dataRowAway["TeamName"].ToString(), Int32.Parse(dataRowAway["AwayTeamScore"].ToString()), Int32.Parse(dataRowHome["HomeTeamScore"].ToString()), i);
}
lstItem.SubItems.Add(dataRowAway["TeamName"].ToString());
lvOverview.Items.Add(lstItem);
}
}
public frmPlayer(Form frm, string un)
{
this.userName = un;
this.ControlBox = false;
frmRanking = frm;
dbh = new DatabaseHandler();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT * FROM [tblUsers] WHERE Username = @username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", userName);
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
this.userID = dr.GetInt32(0);
dr.Close();
}
dbh.CloseConnectionToDB();
InitializeComponent();
if (DisableEditButton())
{
btnEditPrediction.Enabled = false;
}
ShowResults();
ShowScoreCard();
this.Text = "Welcome " + un;
}
//using (SqlCommand cmd = new SqlCommand("SELECT Id FROM TblUsers WHERE Username = @username", dbh.GetCon()))
//{
// cmd.Parameters.AddWithValue("@username", userName);
// int userId = (int)cmd.ExecuteScalar();
// return userId;
//}
private void button1_Click(object sender, EventArgs e, string un)
{
//SoundPlayer simpleSound = new SoundPlayer(@"C:\Users\Gebruiker\Documents\GitHub\project_fifa\mysite_downloads\ProjectFifaV2\Sounds\Chaching.wav");
//simpleSound.Play();
int numberOfGoalsHome = Convert.ToInt32(this.numberOfGoalsHome.Text);
int numberOfGoalsAway = Convert.ToInt32(this.numberOfGoalsAway.Text);
int numberOfPoints = Convert.ToInt32(betAmount.Value);
dbh.TestConnection();
dbh.OpenConnectionToDB();
int Id;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", un);
Id = (int)cmd.ExecuteScalar();
}
int Game_id = Convert.ToInt32(game_id.Text);
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblPredictions ( User_id, Game_id, PredictedHomeScore, PredictedAwayScore) VALUES (@user_id, @Game_id, @PredictedHomeScore, @PredictedAwayScore)"))
{
cmd.Parameters.AddWithValue("@user_id", Id);
cmd.Parameters.AddWithValue("@Game_id", Game_id);
cmd.Parameters.AddWithValue("PredictedHomeScore", numberOfGoalsHome);
cmd.Parameters.AddWithValue("PredictedAwayScore", numberOfGoalsAway);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
dbh.CloseConnectionToDB();
}
private void ShowScore()
{
dbh.CloseConnectionToDB();
dbh.OpenConnectionToDB();
DataTable table = dbh.FillDT("SELECT Username, Score FROM tblUsers WHERE (IsAdmin = 0) ORDER BY Score DESC");
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow dataRow = table.Rows[i];
ListViewItem lstItem = new ListViewItem((i + 1).ToString());
lstItem.SubItems.Add(dataRow["Username"].ToString());
lstItem.SubItems.Add(dataRow["Score"].ToString());
lvRanking.Items.Add(lstItem);
}
dbh.CloseConnectionToDB();
}
private void btnRegister_Click(object sender, EventArgs e)
{
if (txtUsername.Text == "" || txtPassword.Text == "")
{
MessageBox.Show("Both fields are required");
}
else
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
MessageHandler.ShowMessage("This user already exists.");
}
else
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblUsers] ([Username], [Password], [IsAdmin], [Score]) VALUES (@Username, @Password, @IsAdmin, @Score)"))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
cmd.Parameters.AddWithValue("Password", txtPassword.Text);
cmd.Parameters.AddWithValue("IsAdmin", 0);
cmd.Parameters.AddWithValue("Score", 0);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
txtUsername.Text = "";
txtPassword.Text = "";
MessageBox.Show("Succesfully registered");
}
}
dbh.CloseConnectionToDB();
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(string.IsNullOrEmpty(txtPath.Text)))
{
dbh.OpenConnectionToDB();
BindDataCSV(txtPath.Text);
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnClearPrediction_Click(object sender, EventArgs e)
{
int userID = this.userID;
DialogResult result = MessageBox.Show("Are you sure you want to clear your prediction?", "Clear Predictions", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM tblPredictions WHERE User_ID = @userID;", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("UserID", userID);
dbh.OpenConnectionToDB();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
MessageBox.Show("Your predictions have been removed");
}
}
}
private void btnExit_Click(object sender, EventArgs e)
{
// Gives the option to quit the application.
DialogResult result = MessageBox.Show("Are you sure you want to quit?", "Quit", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result.Equals(DialogResult.Yes))
{
if (dbh.GetCon().State == ConnectionState.Open)
{
dbh.CloseConnectionToDB();
}
Application.Exit();
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
// This is letting us to load in a CSV file.
if (!(txtPath.Text == null))
{
string sql = "BULK INSERT TblTeams" +
" FROM '" + txtPath.Text + "'" +
"WITH" +
"(" +
" FIRSTROW = 2," +
" FIELDTERMINATOR = ',', " +
" ROWTERMINATOR = '\n', " +
" TABLOCK" +
")";
dbh.OpenConnectionToDB();
ExecuteSQL(sql);
dbh.CloseConnectionToDB();
// This disables a couple of buttons and/or text boxes to be sure that an exception won't happen.
btnExecute.Enabled = true;
btnLoadData.Enabled = false;
btnSelectFile.Enabled = false;
txtQuery.Enabled = true;
txtPath.Enabled = false;
}
else
{
// This shows a message if nothing is selected.
MessageHandler.ShowMessage("No filename selected.");
}
}
private void button1_Click(object sender, EventArgs e)
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
DataTable hometable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.HomeTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.HomeTeam = tblTeams.Team_ID");
DataTable awayTable = dbh.FillDT("SELECT tblTeams.TeamName, tblGames.AwayTeamScore FROM tblGames INNER JOIN tblTeams ON tblGames.AwayTeam = tblTeams.Team_ID");
dbh.CloseConnectionToDB();
for (int i = 0; i < hometable.Rows.Count; i++)
{
DataRow dataRowHome = hometable.Rows[i];
DataRow dataRowAway = awayTable.Rows[i];
if (dataRowHome["HomeTeamScore"] != null && dataRowAway["AwayTeamScore"] != null)
{
ListViewItem lstItem = new ListViewItem(dataRowHome["TeamName"].ToString());
lstItem.SubItems.Add(dataRowHome["HomeTeamScore"].ToString());
lstItem.SubItems.Add(dataRowAway["AwayTeamScore"].ToString());
lstItem.SubItems.Add(dataRowAway["TeamName"].ToString());
lvOverview.Items.Add(lstItem);
}
}
}
private void btnRegister_Click(object sender, EventArgs e)
{
//SoundPlayer simpleSound = new SoundPlayer(@"C:\Users\Gebruiker\Documents\GitHub\project_fifa\mysite_downloads\ProjectFifaV2\Sounds\button_click.wav");
//simpleSound.Play();
if (txtUsername.Text == "" || txtPassword.Text == "")
{
MessageBox.Show("Both fields are required");
}
else
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
bool exist = false;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
MessageHandler.ShowMessage("This user already exists.");
}
else
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblUsers] ([Username], [Password], [IsAdmin],[Score]) VALUES (@Username, @Password, @IsAdmin, @score)"))
{
cmd.Parameters.AddWithValue("Username", txtUsername.Text);
cmd.Parameters.AddWithValue("Password", txtPassword.Text);
cmd.Parameters.AddWithValue("IsAdmin", 0);
cmd.Parameters.AddWithValue("score", 0);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
string username = txtUsername.Text;
string password = txtPassword.Text;
txtUsername.Text = "";
txtPassword.Text = "";
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblUsers] WHERE Username = @Username AND Password = @Password", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
cmd.Parameters.AddWithValue("Password", password);
exist = (int)cmd.ExecuteScalar() > 0;
}
if (exist)
{
bool admin;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) from [tblUsers] WHERE Username = @Username AND IsAdmin = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", username);
admin = (int)cmd.ExecuteScalar() > 0;
}
dbh.CloseConnectionToDB();
if (admin)
{
frmAdmin.Show();
}
else
{
frmPlayer = new frmPlayer(frmRanking, username);
frmPlayer.Show();
//frmPlayer.Show();
}
}
else
{
dbh.CloseConnectionToDB();
MessageHandler.ShowMessage("Wrong username and/or password.");
}
}
dbh.CloseConnectionToDB();
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == ""))
{
dbh.OpenConnectionToDB();
MessageHandler.ShowMessage(txtPath.Text);
List <string> listA = new List <string>();
List <string> listB = new List <string>();
List <string> listC = new List <string>();
List <string> listD = new List <string>();
List <string> listE = new List <string>();
List <string> listF = new List <string>();
using (var reader = new StreamReader(txtPath.Text))
{
if (RB_load_matches.Checked)
{
string query = "delete from tblgames";
ExecuteSQL(query);
}
if (RB_load_teams.Checked)
{
string query = "delete from tblteams";
ExecuteSQL(query);
}
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(',');
if (RB_load_matches.Checked)
{
bool score = false;
string id = values[0];
string home_team = values[1];
string away_team = values[2];
string home_team_score = values[3];
string away_team_score = values[4];
id = id.Replace("\"", "");
int game_id = Convert.ToInt32(id);
home_team = home_team.Replace("\"", "");
int home_team_id = Convert.ToInt32(home_team);
away_team = away_team.Replace("\"", "");
int away_team_id = Convert.ToInt32(away_team);
if (home_team_score == "NULL")
{
home_team_score = away_team.Replace("\"", "");
int home_team_score_int = Convert.ToInt32(home_team_score);
away_team_score = away_team.Replace("\"", "");
int away_team_score_int = Convert.ToInt32(away_team_score);
score = true;
string query = "insert into Tblgames (game_id, hometeam, awayteam, hometeamscore, awayteamscore) values ('" + game_id + "','" + home_team_id + "','" + away_team_id + "','" + home_team_score_int + "','" + away_team_score_int + "')";
dbh.FillDT(query);
MessageHandler.ShowMessage("loaded the matches");
}
if (score == true)
{
string query = "insert into Tblgames (game_id, hometeam, awayteam) values ('" + game_id + "','" + home_team_id + "','" + away_team_id + "')";
dbh.FillDT(query);
}
}
else if (RB_load_teams.Checked)
{
string item = values[0];
string itemB = values[2];
string B = item.Replace("\"", "");
int A = Convert.ToInt32(B);
string query = "insert into Tblteams (team_id, teamname) values ('" + A + "','" + itemB + "')";
dbh.FillDT(query);
MessageHandler.ShowMessage("loaded the teams");
}
else
{
MessageHandler.ShowMessage("select an button");
}
}
}
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
public frmPlayer(Form frm, string un)
{
// This is letting the user to see the preditions, result and scorecard. We need an dbh to excute sqls.
int amount = dbh.DTInt("SELECT COUNT(*) FROM TblGames");
rowLeft = new NumericUpDown[amount];
rowRight = new NumericUpDown[amount];
this.ControlBox = false;
frmRanking = frm;
this.counter--;
dbh = new DatabaseHandler();
InitializeComponent();
// Disables buttons if its passed it expire date.
if (DisableEditButton())
{
btnClearPrediction.Enabled = false;
btnSaveButton.Enabled = false;
}
this.Text = un;
// Checks if some preditions already has been saved.
DataTable tblUsers = dbh.FillDT("SELECT * FROM TblUsers WHERE (Username='******')");
dbh.TestConnection();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT id FROM TblUsers WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", this.Text);
string sql = Convert.ToString(cmd.ExecuteScalar());
int.TryParse(sql, out this.resultId);
}
int userId = resultId;
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblPredictions] WHERE User_id = @User_id AND Saved = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("User_id", userId);
saved = (int)cmd.ExecuteScalar() > 0;
}
dbh.CloseConnectionToDB();
if (saved)
{
btnSaveButton.Enabled = false;
}
else
{
btnClearPrediction.Enabled = false;
}
ShowResults();
ShowScoreCard();
ShowPredictions(userId);
}
private void ViewMatches()
{
string checkup = "SELECT * FROM tblGames WHERE finished = 0";
DataTable dtCheck = dbh.FillDT(checkup);
if (dtCheck.Rows.Count > 0)
{
string countPoules = "SELECT DISTINCT pouleId FROM tblGames";
DataTable pouleCount = dbh.FillDT(countPoules);
string query = "SELECT team1.teamName AS Home, team2.teamName AS Away, game.HomeTeamScore AS HomeScore, game.AwayTeamScore AS AwayScore, game.Game_ID AS Game_ID FROM ((tblGames AS game INNER JOIN tblTeams AS team1 ON game.HomeTeam = team1.teamNr AND game.PouleId = team1.PouleId) INNER JOIN tblTeams AS team2 ON game.AwayTeam = team2.teamNr AND game.PouleID = team2.PouleId) ORDER BY game.PouleId, game.Game_Id ASC";
DataTable results = dbh.FillDT(query);
dbh.CloseConnectionToDB();
lengthOutterArray = results.Rows.Count;
rows = new TextBox[lengthOutterArray, lengthInnerArray];
index = new int[lengthOutterArray];
for (int i = 0; i < results.Rows.Count; i++)
{
DataRow match = results.Rows[i];
Label lblHomeTeam = new Label();
Label lblAwayTeam = new Label();
TextBox txtHomePred = new TextBox();
TextBox txtAwayPred = new TextBox();
lblHomeTeam.TextAlign = ContentAlignment.BottomRight;
lblHomeTeam.Text = match["Home"].ToString();
lblHomeTeam.Location = new Point(15, txtHomePred.Bottom + (i * 30));
lblHomeTeam.AutoSize = true;
txtHomePred.Text = "";
txtHomePred.Location = new Point(lblHomeTeam.Width, lblHomeTeam.Top - 3);
txtHomePred.Width = 40;
rows[i, 0] = txtHomePred;
txtAwayPred.Text = "";
txtAwayPred.Location = new Point(txtHomePred.Width + lblHomeTeam.Width, txtHomePred.Top);
txtAwayPred.Width = 40;
rows[i, 1] = txtAwayPred;
int.TryParse(match["Game_ID"].ToString(), out index[i]);
lblAwayTeam.Text = match["Away"].ToString();
lblAwayTeam.Location = new Point(txtHomePred.Width + lblHomeTeam.Width + txtAwayPred.Width, txtHomePred.Top + 3);
lblAwayTeam.AutoSize = true;
pnlScores.Controls.Add(lblHomeTeam);
pnlScores.Controls.Add(txtHomePred);
pnlScores.Controls.Add(txtAwayPred);
pnlScores.Controls.Add(lblAwayTeam);
}
}
else
{
string quarterQuery = "SELECT * FROM tblPlayoffs WHERE playoffRankingA = 0 AND playoffRankingB = 0 AND finished = 0";
DataTable quarterFinals = dbh.FillDT(quarterQuery);
string semiQuery = "SELECT * FROM tblPlayoffs WHERE playoffRankingA = 1 AND playoffRankingB = 1 AND finished = 0";
DataTable semiFinals = dbh.FillDT(semiQuery);
string finalQuery = "SELECT * FROM tblPlayoffs WHERE playoffRankingA = 2 AND playoffRankingB = 2 AND finished = 0";
DataTable finals = dbh.FillDT(finalQuery);
if (quarterFinals.Rows.Count > 0)
{
lengthOutterArray = quarterFinals.Rows.Count;
rows = new TextBox[lengthOutterArray, lengthInnerArray];
index = new int[lengthOutterArray];
for (int i = 0; i < quarterFinals.Rows.Count; i++)
{
object[,] obj = { { "id", quarterFinals.Rows[i]["pouleIdA"] }, { "poule", quarterFinals.Rows[i]["pouleRankingA"] } };
string quarterIntern1a = "SELECT * FROM tblTeams WHERE pouleId=@id AND pouleRanking = @poule";
DataTable quarterIntern1b = dbh.FillDT(quarterIntern1a, obj);
object[,] obj2 = { { "id", quarterFinals.Rows[i]["pouleIdB"] }, { "poule", quarterFinals.Rows[i]["pouleRankingB"] } };
string quarterIntern2a = "SELECT * FROM tblTeams WHERE pouleId=@id AND pouleRanking = @poule";
DataTable quarterIntern2b = dbh.FillDT(quarterIntern2a, obj2);
//if (quarterFinals.Rows[i]["finished"].ToString() == "0")
//{
DataRow match = quarterFinals.Rows[i];
Label lblHomeTeam = new Label();
Label lblAwayTeam = new Label();
TextBox txtHomePred = new TextBox();
TextBox txtAwayPred = new TextBox();
lblHomeTeam.TextAlign = ContentAlignment.BottomRight;
lblHomeTeam.Text = quarterIntern1b.Rows[0]["teamName"].ToString();
lblHomeTeam.Location = new Point(15, txtHomePred.Bottom + (i * 30));
lblHomeTeam.AutoSize = true;
txtHomePred.Text = "";
txtHomePred.Location = new Point(lblHomeTeam.Width, lblHomeTeam.Top - 3);
txtHomePred.Width = 40;
rows[i, 0] = txtHomePred;
txtAwayPred.Text = "";
txtAwayPred.Location = new Point(txtHomePred.Width + lblHomeTeam.Width, txtHomePred.Top);
txtAwayPred.Width = 40;
rows[i, 1] = txtAwayPred;
int.TryParse(match["id"].ToString(), out index[i]);
lblAwayTeam.Text = quarterIntern2b.Rows[0]["teamName"].ToString();
lblAwayTeam.Location = new Point(txtHomePred.Width + lblHomeTeam.Width + txtAwayPred.Width, txtHomePred.Top + 3);
lblAwayTeam.AutoSize = true;
pnlScores.Controls.Add(lblHomeTeam);
pnlScores.Controls.Add(txtHomePred);
pnlScores.Controls.Add(txtAwayPred);
pnlScores.Controls.Add(lblAwayTeam);
}
}
if (quarterFinals.Rows.Count == 0)
{
lengthOutterArray = semiFinals.Rows.Count;
rows = new TextBox[lengthOutterArray, lengthInnerArray];
index = new int[lengthOutterArray];
for (int i = 0; i < semiFinals.Rows.Count; i++)
{
object[,] obj = { { "id", semiFinals.Rows[i]["pouleIdA"] }, { "rank", semiFinals.Rows[i]["pouleRankingA"] } };
string semiIntern1a = "SELECT * FROM tblTeams WHERE pouleId=@id AND pouleRanking = @rank";
DataTable semiIntern1b = dbh.FillDT(semiIntern1a, obj);
object[,] obj2 = { { "id", semiFinals.Rows[i]["PouleIdB"] }, { "rank", semiFinals.Rows[i]["pouleRankingB"] } };
string semiIntern2a = "SELECT * FROM tblTeams WHERE pouleId=@id AND pouleRanking = @rank";
DataTable semiIntern2b = dbh.FillDT(semiIntern2a, obj2);
DataRow match = semiFinals.Rows[i];
Label lblHomeTeam = new Label();
Label lblAwayTeam = new Label();
TextBox txtHomePred = new TextBox();
TextBox txtAwayPred = new TextBox();
lblHomeTeam.TextAlign = ContentAlignment.BottomRight;
lblHomeTeam.Text = semiIntern1b.Rows[0]["teamName"].ToString();
lblHomeTeam.Location = new Point(15, txtHomePred.Bottom + (i * 30));
lblHomeTeam.AutoSize = true;
txtHomePred.Text = "";
txtHomePred.Location = new Point(lblHomeTeam.Width, lblHomeTeam.Top - 3);
txtHomePred.Width = 40;
rows[i, 0] = txtHomePred;
txtAwayPred.Text = "";
txtAwayPred.Location = new Point(txtHomePred.Width + lblHomeTeam.Width, txtHomePred.Top);
txtAwayPred.Width = 40;
rows[i, 1] = txtAwayPred;
int.TryParse(match["id"].ToString(), out index[i]);
lblAwayTeam.Text = semiIntern2b.Rows[0]["teamName"].ToString();
lblAwayTeam.Location = new Point(txtHomePred.Width + lblHomeTeam.Width + txtAwayPred.Width, txtHomePred.Top + 3);
lblAwayTeam.AutoSize = true;
pnlScores.Controls.Add(lblHomeTeam);
pnlScores.Controls.Add(txtHomePred);
pnlScores.Controls.Add(txtAwayPred);
pnlScores.Controls.Add(lblAwayTeam);
}
}
if (quarterFinals.Rows.Count == 0 && semiFinals.Rows.Count == 0)
{
lengthOutterArray = finals.Rows.Count;
rows = new TextBox[lengthOutterArray, lengthInnerArray];
index = new int[lengthOutterArray];
for (int i = 0; i < finals.Rows.Count; i++)
{
object[,] obj = { { "id", finals.Rows[i]["PouleIdA"] }, { "rank", finals.Rows[i]["pouleRankingA"] } };
string finalIntern1a = "SELECT * FROM tblTeams WHERE pouleId=@id AND pouleRanking =@rank";
DataTable finalIntern1b = dbh.FillDT(finalIntern1a, obj);
object[,] obj2 = { { "id", finals.Rows[i]["PouleIdB"] }, { "rank", finals.Rows[i]["pouleRankingB"] } };
string finalIntern2a = "SELECT * FROM tblTeams WHERE pouleId=@id AND pouleRanking = @rank";
DataTable finalIntern2b = dbh.FillDT(finalIntern2a, obj2);
DataRow match = finals.Rows[i];
Label lblHomeTeam = new Label();
Label lblAwayTeam = new Label();
TextBox txtHomePred = new TextBox();
TextBox txtAwayPred = new TextBox();
lblHomeTeam.TextAlign = ContentAlignment.BottomRight;
lblHomeTeam.Text = finalIntern1b.Rows[0]["teamName"].ToString();
lblHomeTeam.Location = new Point(15, txtHomePred.Bottom + (i * 30));
lblHomeTeam.AutoSize = true;
txtHomePred.Text = "";
txtHomePred.Location = new Point(lblHomeTeam.Width, lblHomeTeam.Top - 3);
txtHomePred.Width = 40;
rows[i, 0] = txtHomePred;
txtAwayPred.Text = "";
txtAwayPred.Location = new Point(txtHomePred.Width + lblHomeTeam.Width, txtHomePred.Top);
txtAwayPred.Width = 40;
rows[i, 1] = txtAwayPred;
int.TryParse(match["id"].ToString(), out index[i]);
lblAwayTeam.Text = finalIntern2b.Rows[0]["teamName"].ToString();
lblAwayTeam.Location = new Point(txtHomePred.Width + lblHomeTeam.Width + txtAwayPred.Width, txtHomePred.Top + 3);
lblAwayTeam.AutoSize = true;
pnlScores.Controls.Add(lblHomeTeam);
pnlScores.Controls.Add(txtHomePred);
pnlScores.Controls.Add(txtAwayPred);
pnlScores.Controls.Add(lblAwayTeam);
}
}
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
string[] tables = new string[3];
tables[0] = "[tblGames]";
tables[1] = "[tblPlayers]";
tables[2] = "[tblTeams]";
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
DialogResult result = MessageBox.Show("Are you sure you want to clear the database?", "Clear database", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
{
DataTable hometable = dbh.FillDT("DELETE FROM [tblGames];");
dbh.FillDT("DELETE FROM [tblPlayers];");
dbh.FillDT("DELETE FROM [tblTeams];");
using (var fs = File.OpenRead(path))
using (var reader = new StreamReader(fs))
{
string[] values = new string[0];
int number = 0;
List <string> numbers = new List <string>();
List <string> valueList;
for (int i = -1; i < tables.Length;)
{
while (!reader.EndOfStream)
{
//split the lines for proper values
string line = reader.ReadLine();
values = line.Split(',', ';', '"');
valueList = new List <string>(values);
//remove any whitespaces
for (int j = 0; j < values.Count(); j++)
{
valueList.Remove("");
}
numbers = new List <string>(valueList.Count());
//change values from csv to integers if possible
for (int j = 0; j < valueList.Count(); j++)
{
if (int.TryParse(valueList[j], out number))
{
numbers.Add(number.ToString());
}
else if (valueList[j] == "NULL")
{
valueList[j] = 0.ToString();
numbers.Add(valueList[j]);
}
else
{
numbers.Add(valueList[j]);
}
}
//check if the value starts with this stringvalue then switch database
if (valueList[0].StartsWith("1") || reader.EndOfStream)
{
i++;
}
break;
}
if (i == 0)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblGames] ([HomeTeam], [AwayTeam], [HomeTeamScore], [AwayTeamScore]) VALUES (@HomeTeam, @AwayTeam, @HomeTeamScore, @AwayTeamScore)"))
{
//parameters for tblGames
cmd.Parameters.AddWithValue("HomeTeam", numbers[1]);
cmd.Parameters.AddWithValue("AwayTeam", numbers[2]);
cmd.Parameters.AddWithValue("HomeTeamScore", numbers[3]);
cmd.Parameters.AddWithValue("AwayTeamScore", numbers[4]);
dbh.OpenConnectionToDB();
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
}
if (i == 1)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblPlayers] ([Name], [Surname], [GoalsScored], [Team_id]) VALUES (@name, @surname, @goalsScored, @teamID)"))
{
//parameters for tblPlayers
cmd.Parameters.AddWithValue("name", numbers[3]);
cmd.Parameters.AddWithValue("surname", numbers[4]);
cmd.Parameters.AddWithValue("goalsScored", 0);
cmd.Parameters.AddWithValue("teamID", numbers[2]);
dbh.OpenConnectionToDB();
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
}
if (i == 2 || i == 3)
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO [tblTeams] ([Team_id], [TeamName]) VALUES (@TeamID, @TeamName)"))
{
//parameters for tblTeams
cmd.Parameters.AddWithValue("TeamID", numbers[0]);
cmd.Parameters.AddWithValue("TeamName", numbers[2]);
dbh.OpenConnectionToDB();
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
dbh.CloseConnectionToDB();
}
}
}
}
}
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void ShowScore()
{
dbh.TestConnection();
dbh.OpenConnectionToDB();
int userID = 0;
int score = 0;
int matches;
DataTable users = dbh.FillDT("SELECT Username, Score FROM tblUsers WHERE (IsAdmin = 0) ORDER BY Score DESC");
//using (SqlCommand cmd = new SqlCommand("SELECT COUNT(Game_id) FROM tblGames"))
//{
// dbh.OpenConnectionToDB();
// cmd.Connection = dbh.GetCon();
// matches = (int)cmd.ExecuteScalar();
// dbh.CloseConnectionToDB();
//}
//DataTable scoreHome = dbh.FillDT("SELECT HomeTeamScore FROM tblGames");
//DataTable scoreAway = dbh.FillDT("SELECT AwayTeamScore FROM tblGames");
//List<int> predictedHomeScores = new List<int>();
//List<int> predictedAwayScores = new List<int>();
//using (SqlCommand cmd = new SqlCommand("SELECT predictedHomeScore FROM tblPredictions WHERE User_ID = @userID"))
//{
// dbh.OpenConnectionToDB();
// cmd.Connection = dbh.GetCon();
// cmd.Parameters.AddWithValue("userID", userID);
// using (SqlDataReader objReader = cmd.ExecuteReader())
// {
// if(objReader.HasRows)
// {
// while(objReader.Read())
// {
// int item = objReader.GetInt32(objReader.GetOrdinal("predictedHomeScore"));
// predictedHomeScores.Add(item);
// }
// }
// }
// dbh.CloseConnectionToDB();
//}
//using (SqlCommand cmd = new SqlCommand("SELECT predictedAwayScore FROM tblPredictions WHERE User_ID = @userID"))
//{
// dbh.OpenConnectionToDB();
// cmd.Connection = dbh.GetCon();
// cmd.Parameters.AddWithValue("userID", userID);
// using (SqlDataReader objReader = cmd.ExecuteReader())
// {
// if (objReader.HasRows)
// {
// while (objReader.Read())
// {
// int item = objReader.GetInt32(objReader.GetOrdinal("predictedHomeScore"));
// predictedHomeScores.Add(item);
// }
// }
// }
// dbh.CloseConnectionToDB();
//}
//for (int i = 0; i < matches; i++)
//{
// if (scoreHome.AsEnumerable().ToString() == predictedHomeScores[i].ToString() && scoreAway.AsEnumerable().ToString() == predictedAwayScores[i].ToString())
// score = score + 2;
// else if (scoreHome.AsEnumerable().ToString() == predictedHomeScores[i].ToString() || scoreAway.AsEnumerable().ToString() == predictedAwayScores[i].ToString())
// score++;
//}
//using (SqlCommand cmd = new SqlCommand("UPDATE [tblUsers] score = @score WHERE id = @userID"))
//{
// cmd.Parameters.AddWithValue("userID", userID);
// cmd.Parameters.AddWithValue("score", score);
//}
for (int i = 0; i < users.Rows.Count; i++)
{
DataRow dataRow = users.Rows[i];
ListViewItem lstItem = new ListViewItem((i + 1).ToString());
lstItem.SubItems.Add(dataRow["Username"].ToString());
lstItem.SubItems.Add(dataRow["Score"].ToString());
lvRanking.Items.Add(lstItem);
}
dbh.CloseConnectionToDB();
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (txtPath.Text != "")
{
string[] pathSplit = txtPath.Text.Split('\\');
int latestIndex = pathSplit.Length - 1;
StreamReader sr;
bool success = true;
string fileName = pathSplit[latestIndex];
dbh.OpenConnectionToDB();
try
{
sr = new StreamReader(txtPath.Text);
}
catch (System.IO.DirectoryNotFoundException)
{
MessageHandler.ShowMessage(string.Format("Couldn't find the directory."));
success = false;
}
catch (System.IO.FileNotFoundException)
{
MessageHandler.ShowMessage("Couldn't find the file.");
success = false;
}
catch (System.ArgumentException)
{
MessageHandler.ShowMessage("Unkown path", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
success = false;
}
if (success)
{
sr = new StreamReader(txtPath.Text);
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
foreach (string dc in value)
{
dt.Columns.Add(new DataColumn(dc));
}
while (!sr.EndOfStream)
{
value = sr.ReadLine().Split(',');
if (value.Length == dt.Columns.Count)
{
DataRow row = dt.NewRow();
row.ItemArray = value;
dt.Rows.Add(row);
}
else
{
MessageHandler.ShowMessage("Amount of columns not consistent");
return;
}
}
SqlBulkCopy bc = new SqlBulkCopy(dbh.GetConnectionString(), SqlBulkCopyOptions.TableLock);
if (!fileName.Contains("csv"))
{
MessageHandler.ShowMessage("This isn't a CSV file", "CSV Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (fileName.Contains("teams"))
{
bc.DestinationTableName = "TblTeams";
dbh.TruncateTable("TblTeams");
MessageHandler.ShowMessage("Teams toegevoegd");
}
else if (fileName.Contains("matches"))
{
bc.DestinationTableName = "TblGames";
dbh.TruncateTable("TblGames");
MessageHandler.ShowMessage("Matches toegevoegd");
}
else
{
MessageHandler.ShowMessage("There was no matching Table found for this csv file", "CSV Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
bc.BatchSize = dt.Rows.Count;
bc.WriteToServer(dt);
bc.Close();
}
}
dbh.CloseConnectionToDB();
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
StreamReader sr = new StreamReader(txtPath.Text);
if (boxTableType.Text == "")
{
MessageHandler.ShowMessage("you have to select an table.");
}
else if (boxTableType.Text == "matches")
{
string data = sr.ReadLine();
while (data != null)
{
string[] value = data.Split(',');
int homeTeam = Convert.ToInt32(value[1]);
int awayTeam = Convert.ToInt32(value[2]);
int scoreHome = Convert.ToInt32(value[3]);
int scoreAway = Convert.ToInt32(value[4]);
data = sr.ReadLine();
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblGames ( HomeTeam, AwayTeam, HomeTeamScore, AwayTeamScore) VALUES (@homeTeam, @awayTeam, @scoreHome, @scoreAway)"))
{
cmd.Parameters.AddWithValue("@homeTeam", homeTeam);
cmd.Parameters.AddWithValue("@awayTeam", awayTeam);
cmd.Parameters.AddWithValue("@scoreHome", scoreHome);
cmd.Parameters.AddWithValue("@scoreAway", scoreAway);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
}
else if (boxTableType.Text == "players")
{
string data = sr.ReadLine();
while (data != null)
{
string[] value = data.Split(',');
int id = Convert.ToInt32(value[0]);
string name = value[3];
string newName = name.Trim('"');
string surname = value[4];
string newSurname = surname.Trim('"');
int goalScored = Convert.ToInt32(value[5]);
int team_id = Convert.ToInt32(value[2]);
data = sr.ReadLine();
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblPlayers (Name, Surname, GoalsScored, Team_id) VALUES (@name, @surname, @goalScored, @Team_id)"))
{
cmd.Parameters.AddWithValue("@name", newName);
cmd.Parameters.AddWithValue("@surname", newSurname);
cmd.Parameters.AddWithValue("@goalScored", goalScored);
cmd.Parameters.AddWithValue("@Team_id", team_id);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
}
else if (boxTableType.Text == "teams")
{
string data = sr.ReadLine();
while (data != null)
{
string[] value = data.Split(',');
string TeamName = value[2];
data = sr.ReadLine();
using (SqlCommand cmd = new SqlCommand("INSERT INTO TblTeams (TeamName) VALUES (@teamName)"))
{
cmd.Parameters.AddWithValue("@teamName", TeamName);
cmd.Connection = dbh.GetCon();
cmd.ExecuteNonQuery();
}
}
}
MessageHandler.ShowMessage("data insert has been succeeded");
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}
private void btnClearPrediction_Click(object sender, EventArgs e)
{
// This is letting the user to clear his/her preditions.
DialogResult result = MessageBox.Show("Are you sure you want to clear your prediction?", "Clear Predictions", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
if (result.Equals(DialogResult.Yes))
{
// We are trying to get the users id to make sure we delete his/her preditions.
DataTable tblUsers = dbh.FillDT("SELECT * FROM TblUsers WHERE (Username='******')");
dbh.TestConnection();
dbh.OpenConnectionToDB();
using (SqlCommand cmd = new SqlCommand("SELECT id FROM TblUsers WHERE Username = @Username", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("Username", this.Text);
string sql = Convert.ToString(cmd.ExecuteScalar());
int.TryParse(sql, out this.resultId);
}
dbh.CloseConnectionToDB();
int userId = resultId;
int counterCounts = 0;
string home = "";
string away = "";
string sqlStr = "DELETE FROM TblPredictions WHERE user_id ='" + userId + "'";
for (; counterCounts < lengthOutterArray; counterCounts++)
{
home = rowLeft[counterCounts].Text;
away = rowRight[counterCounts].Text;
}
dbh.Execute(sqlStr);
lvPredictions.Items.Clear();
ShowPredictions(userId);
dbh.TestConnection();
dbh.OpenConnectionToDB();
// Making sure that 0 predictions from the user will stay in the database.
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [tblPredictions] WHERE User_id = @User_id AND Saved = 1", dbh.GetCon()))
{
cmd.Parameters.AddWithValue("User_id", userId);
saved = (int)cmd.ExecuteScalar() > 0;
}
if (saved)
{
MessageBox.Show("Er gaat iets fout...");
}
else
{
btnSaveButton.Enabled = true;
btnClearPrediction.Enabled = false;
}
dbh.CloseConnectionToDB();
}
}
private void ShowResults()
{
string checkup = "SELECT * FROM tblGames WHERE finished = 0";
DataTable dtCheck = dbh.FillDT(checkup);
if (dtCheck.Rows.Count > 0)
{
string countPoules = "SELECT DISTINCT pouleId FROM tblGames";
DataTable pouleCount = dbh.FillDT(countPoules);
for (int i = 0; i < pouleCount.Rows.Count; i++)
{
DataRow dr = pouleCount.Rows[i];
lvOverviewP1.Groups.Add("poule" + i + "Group", "Poule " + dr["pouleId"].ToString());
}
string query = "SELECT team1.teamName AS Home, game.HomeTeamScore AS HomeScore, game.AwayTeamScore AS AwayScore, team2.teamName AS Away, game.PouleId as poule FROM ((tblGames AS game INNER JOIN tblTeams AS team1 ON game.HomeTeam = team1.teamNr AND game.PouleId = team1.PouleId) INNER JOIN tblTeams AS team2 ON game.AwayTeam = team2.teamNr AND game.PouleId = team2.PouleId) ORDER BY game.PouleId, game.Game_Id ASC;";
DataTable results = dbh.FillDT(query);
dbh.CloseConnectionToDB();
foreach (DataRow match in results.Rows)
{
ListViewItem lstItem = new ListViewItem(match["Home"].ToString());
lstItem.SubItems.Add(match["HomeScore"].ToString());
lstItem.SubItems.Add(match["AwayScore"].ToString());
lstItem.SubItems.Add(match["Away"].ToString());
lstItem.SubItems.Add(match["poule"].ToString());
int poule;
int.TryParse(match["poule"].ToString(), out poule);
poule--;
lstItem.Group = lvOverviewP1.Groups[poule];
lvOverviewP1.Items.Add(lstItem);
}
}
else
{
lvOverviewP1.Groups.Add("quarterFinals", "Quarter Finals");
lvOverviewP1.Groups.Add("semiFinals", "Semi-Finals");
lvOverviewP1.Groups.Add("finals", "Finals");
string quarterQuery = "SELECT * FROM tblPlayoffs WHERE playoffRankingA = 0 AND playoffRankingB = 0 AND finished = 0";
DataTable quarterFinals = dbh.FillDT(quarterQuery);
string semiQuery = "SELECT * FROM tblPlayoffs WHERE playoffRankingA = 1 AND playoffRankingB = 1 AND finished = 0";
DataTable semiFinals = dbh.FillDT(semiQuery);
string finalQuery = "SELECT * FROM tblPlayoffs WHERE playoffRankingA = 2 AND playoffRankingB = 2 AND finished = 0";
DataTable finals = dbh.FillDT(finalQuery);
foreach (DataRow quarterFinal in quarterFinals.Rows)
{
object[,] quarter1d = { { "quarter", quarterFinal["pouleRankingA"] } };
string quarterIntern1a = "SELECT * FROM tblTeams WHERE pouleRanking = @quarter";
DataTable quarterIntern1b = dbh.FillDT(quarterIntern1a, quarter1d);
int quarterCount1c = quarterIntern1b.Rows.Count;
object[,] quarter2d = { { "quarter", quarterFinal["pouleRankingB"] } };
string quarterIntern2a = "SELECT * FROM tblTeams WHERE pouleRanking = @quarter";
DataTable quarterIntern2b = dbh.FillDT(quarterIntern2a, quarter2d);
int quarterCount2c = quarterIntern2b.Rows.Count;
if (quarterCount1c > 0 && quarterCount2c > 0)
{
object[,] team1 = { { "quarter", quarterFinal["pouleIdA"] }, { "rank", quarterFinal["pouleRankingA"] } };
string teamAsql = "SELECT * FROM tblTeams WHERE pouleId = @quarter AND pouleRanking = @rank";
DataTable teamAdt = dbh.FillDT(teamAsql, team1);
object[,] team2 = { { "quarter", quarterFinal["pouleIdB"] }, { "rank", quarterFinal["pouleRankingB"] } };
string teamBsql = "SELECT * FROM tblTeams WHERE pouleId = @quarter AND pouleRanking = @rank";
DataTable teamBdt = dbh.FillDT(teamBsql, team2);
for (int i = 0; i < teamAdt.Rows.Count; i++)
{
DataRow teamArow = teamAdt.Rows[i];
DataRow teamBrow = teamBdt.Rows[i];
ListViewItem lstItem = new ListViewItem(teamArow["teamName"].ToString());
lstItem.SubItems.Add(quarterFinal["scoreHomeTeam"].ToString());
lstItem.SubItems.Add(quarterFinal["scoreAwayTeam"].ToString());
lstItem.SubItems.Add(teamBrow["teamName"].ToString());
lstItem.Group = lvOverviewP1.Groups[0];
lvOverviewP1.Items.Add(lstItem);
}
}
}
foreach (DataRow semiFinal in semiFinals.Rows)
{
if (quarterFinals.Rows.Count == 0)
{
object[,] team1 = { { "rank", semiFinal["pouleRankingA"] } };
string semiIntern1a = "SELECT * FROM tblTeams WHERE pouleRanking =@rank";
DataTable semiIntern1b = dbh.FillDT(semiIntern1a, team1);
int semiCount1c = semiIntern1b.Rows.Count;
object[,] team2 = { { "rank", semiFinal["pouleRankingB"] } };
string semiIntern2a = "SELECT * FROM tblTeams WHERE pouleRanking = @rank";
DataTable semiIntern2b = dbh.FillDT(semiIntern2a, team2);
int semiCount2c = semiIntern2b.Rows.Count;
if (semiCount1c > 0 && semiCount2c > 0)
{
object[,] team1b = { { "poule", semiFinal["pouleIdA"] }, { "rank", semiFinal["pouleRankingA"] } };
string teamAsql = "SELECT * FROM tblTeams WHERE pouleId = " + semiFinal["pouleIdA"].ToString() + " AND pouleRanking = " + semiFinal["pouleRankingA"].ToString() + "";
DataTable teamAdt = dbh.FillDT(teamAsql, team1b);
object[,] team2b = { { "poule", semiFinal["pouleIdB"] }, { "rank", semiFinal["pouleRankingB"] } };
string teamBsql = "SELECT * FROM tblTeams WHERE pouleId = " + semiFinal["pouleIdB"] + " AND pouleRanking = " + semiFinal["pouleRankingB"] + "";
DataTable teamBdt = dbh.FillDT(teamBsql, team2b);
ListViewItem lsItem = new ListViewItem("semi-final");
lsItem.Group = lvOverviewP1.Groups[1];
lvOverviewP1.Items.Add(lsItem);
for (int i = 0; i < teamAdt.Rows.Count; i++)
{
DataRow teamArow = teamAdt.Rows[i];
DataRow teamBrow = teamBdt.Rows[i];
ListViewItem lstItem = new ListViewItem(teamArow["teamName"].ToString());
lstItem.SubItems.Add(semiFinal["scoreHomeTeam"].ToString());
lstItem.SubItems.Add(semiFinal["scoreAwayTeam"].ToString());
lstItem.SubItems.Add(teamBrow["teamName"].ToString());
lstItem.Group = lvOverviewP1.Groups[1];
lvOverviewP1.Items.Add(lstItem);
}
}
}
}
foreach (DataRow final in finals.Rows)
{
if (semiFinals.Rows.Count == 0)
{
object[,] team1 = { { "rank", final["pouleRankingA"] } };
string finalIntern1a = "SELECT * FROM tblTeams WHERE pouleRanking = " + final["pouleRankingA"].ToString() + "";
DataTable finalIntern1b = dbh.FillDT(finalIntern1a, team1);
int finalCount1c = finalIntern1b.Rows.Count;
object[,] team2 = { { "rank", final["pouleRankingB"] } };
string finalIntern2a = "SELECT * FROM tblTeams WHERE pouleRanking = " + final["pouleRankingB"].ToString() + "";
DataTable finalIntern2b = dbh.FillDT(finalIntern2a, team2);
int finalCount2c = finalIntern2b.Rows.Count;
if (finalCount1c > 0 && finalCount2c > 0)
{
object[,] team1b = { { "poule", final["pouleIdA"] }, { "rank", final["pouleRankingA"] } };
string teamAsql = "SELECT * FROM tblTeams WHERE pouleId = " + final["pouleIdA"].ToString() + " AND pouleRanking = " + final["pouleRankingA"].ToString() + "";
DataTable teamAdt = dbh.FillDT(teamAsql, team1b);
object[,] team2b = { { "poule", final["pouleIdB"] }, { "rank", final["pouleRankingB"] } };
string teamBsql = "SELECT * FROM tblTeams WHERE pouleId = " + final["pouleIdB"] + " AND pouleRanking = " + final["pouleRankingB"] + "";
DataTable teamBdt = dbh.FillDT(teamBsql, team2b);
ListViewItem lsItem = new ListViewItem("final");
lsItem.Group = lvOverviewP1.Groups[2];
lvOverviewP1.Items.Add(lsItem);
for (int i = 0; i < teamAdt.Rows.Count; i++)
{
DataRow teamArow = teamAdt.Rows[i];
DataRow teamBrow = teamBdt.Rows[i];
ListViewItem lstItem = new ListViewItem(teamArow["teamName"].ToString());
lstItem.SubItems.Add(final["scoreHomeTeam"].ToString());
lstItem.SubItems.Add(final["scoreAwayTeam"].ToString());
lstItem.SubItems.Add(teamBrow["teamName"].ToString());
lstItem.Group = lvOverviewP1.Groups[2];
lvOverviewP1.Items.Add(lstItem);
}
}
}
}
}
}
private void btnLoadData_Click(object sender, EventArgs e)
{
if (!(txtPath.Text == null))
{
dbh.OpenConnectionToDB();
using (var reader = new StreamReader(path))
{
List <string> listA = new List <string>();
List <string> listB = new List <string>();
while (!reader.EndOfStream)
{
var lines = reader.ReadLine();
var line = lines.Replace("\"", "");
var values = line.Split(';');
listA.Add(values[0]);
listB.Add(values[1]);
if (!values[1].Contains("student_id") && !values[1].Contains("poule_id"))
{
foreach (string value in values)
{
dgvAdminData.Columns.Add("Column1", value);
}
while (!values[1].Contains("student_id") && !reader.EndOfStream)
{
lines = reader.ReadLine();
line = lines.Replace("\"", "").Replace("NULL", "0");
values = line.Split(';');
if (values[1].Contains("student_id"))
{
break;
}
ToSQL(values, 1);
dgvAdminData.Rows.Add(values[0], values[1], values[2], values[3], values[4], values[5]);
}
}
if (!values[1].Contains("poule_id") && !values[1].Contains("team_id_a"))
{
foreach (string value in values)
{
dataGridView1.Columns.Add("Column1", value);
}
while (!values[1].Contains("poule_id") && !reader.EndOfStream)
{
lines = reader.ReadLine();
line = lines.Replace("\"", "").Replace("NULL", "0");
values = line.Split(';');
if (values[1].Contains("poule_id"))
{
break;
}
ToSQL(values, 2);
dataGridView1.Rows.Add(values[0], values[1], values[2], values[3], values[4], values[5]);
}
}
if (!values[1].Contains("team_id_a") && !values[1].Contains("student_id"))
{
foreach (string value in values)
{
dataGridView2.Columns.Add("Column1", value);
}
while (!values[1].Contains("team_id_a") && !reader.EndOfStream)
{
lines = reader.ReadLine();
line = lines.Replace("\"", "");
values = line.Split(';');
if (reader.EndOfStream)
{
break;
}
ToSQL(values, 3);
dataGridView2.Rows.Add(values[0], values[1], values[2], values[3], values[4]);
}
}
break;
}
}
dbh.CloseConnectionToDB();
}
else
{
MessageHandler.ShowMessage("No filename selected.");
}
}