public static HistoryValue getHistoryVal(SqlDataReader reader, Dictionary <int, RoundValue> rDict, string field)
{
byte round = (byte)reader["Round"];
string team = field == "HOME" ? reader["HomeTeam"].ToString() : reader["AwayTeam"].ToString();
HistoryValue hVal;
if (round == 1)
{
hVal = new HistoryValue();
}
else if (rDict[round - 1].tDict.ContainsKey(team))
{
hVal = rDict[round - 1].tDict[team];
}
else
{
History.errDict[new ArchiveKey(reader)] = String.Format(" BAD Fixture!!! '{0}' has not played in round {1}.", team, round - 1);
hVal = new HistoryValue();
}
hVal.ScoreA = reader["ScoreA"] is DBNull ? null : (byte?)reader["ScoreA"];
hVal.ScoreH = reader["ScoreH"] is DBNull ? null : (byte?)reader["ScoreH"];
hVal.Field = field;
hVal.Date = (DateTime)reader["Date"];
return(hVal);
}
protected int insert(ArchiveKey aKey, int round, string team, HistoryValue hVal)
{
string q = @"SELECT ID FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season AND Round=@Round AND Team=@Team";
SqlCommand selectCmd = new SqlCommand(q, Connection);
selectCmd.Parameters.AddWithValue("@Country", aKey.Country);
selectCmd.Parameters.AddWithValue("@League", aKey.League);
selectCmd.Parameters.AddWithValue("@Season", aKey.Season);
selectCmd.Parameters.AddWithValue("@Round", round);
selectCmd.Parameters.AddWithValue("@Team", team);
SqlCommand modifyCmd;
using (SqlDataReader reader = selectCmd.ExecuteReader())
{
if (reader.HasRows)
{
q = @"UPDATE dbo.history SET
Country=@Country, League=@League, Season=@Season, Round=@Round, Date=@Date, Field=@Field, Team=@Team,
ScoredH=@ScoredH, ReceivedH=@ReceivedH, ScoredA=@ScoredA, ReceivedA=@ReceivedA, Scored=@Scored, Received=@Received,
Points=@Points, Position=@Position, grLevel=@grLevel, grStrength=@grStrength, OtherTeam=@OtherTeam
WHERE ID=@ID";
modifyCmd = new SqlCommand(q, Connection);
reader.Read();
modifyCmd.Parameters.AddWithValue("@ID", reader[0]);
}
else
{
q = @"INSERT INTO dbo.history (
Country,League,Season,Round,Date,Field,Team,ScoredH,ReceivedH,ScoredA,ReceivedA,Scored,Received,Points,Position,grLevel,grStrength,OtherTeam
) VALUES(
@Country,@League,@Season,@Round,@Date,@Field,@Team,@ScoredH,@ReceivedH,@ScoredA,@ReceivedA,@Scored,@Received,@Points,@Position,@grLevel,@grStrength,@OtherTeam
)";
modifyCmd = new SqlCommand(q, Connection);
}
}
modifyCmd.Parameters.AddWithValue("@Country", aKey.Country);
modifyCmd.Parameters.AddWithValue("@League", aKey.League);
modifyCmd.Parameters.AddWithValue("@Season", aKey.Season);
modifyCmd.Parameters.AddWithValue("@Round", round);
modifyCmd.Parameters.AddWithValue("@Date", hVal.Date);
modifyCmd.Parameters.AddWithValue("@Team", team);
modifyCmd.Parameters.AddWithValue("@Field", hVal.Field);
modifyCmd.Parameters.AddWithValue("@ScoredH", hVal.SumScoredH);
modifyCmd.Parameters.AddWithValue("@ReceivedH", hVal.SumReceivedH);
modifyCmd.Parameters.AddWithValue("@ScoredA", hVal.SumScoredA);
modifyCmd.Parameters.AddWithValue("@ReceivedA", hVal.SumReceivedA);
modifyCmd.Parameters.AddWithValue("@Scored", hVal.SumScored);
modifyCmd.Parameters.AddWithValue("@Received", hVal.SumReceived);
modifyCmd.Parameters.AddWithValue("@Points", hVal.Points);
modifyCmd.Parameters.AddWithValue("@Position", hVal.Position);
modifyCmd.Parameters.AddWithValue("@grLevel", hVal.grLevel);
modifyCmd.Parameters.AddWithValue("@grStrength", (object)hVal.grStrength ?? DBNull.Value);
modifyCmd.Parameters.AddWithValue("@OtherTeam", hVal.OtherTeam);
return(modifyCmd.ExecuteNonQuery());
}
private byte homeHistoryVal(SqlDataReader reader, Dictionary <int, RoundValue> rDict)
{
string hTeam = reader["HomeTeam"].ToString();
HistoryValue hVal = getHistoryVal(reader, rDict, "HOME");
if (hVal.ScoreA != null)
{
hVal.Points += (byte)(hVal.ScoreH == hVal.ScoreA ? 1 : hVal.ScoreH > hVal.ScoreA ? 3 : 0);
}
hVal.OtherTeam = reader["AwayTeam"].ToString();
hVal.SumReceived += hVal.ScoreA ?? 0;
hVal.SumReceivedH += hVal.ScoreA ?? 0;
hVal.SumScored += hVal.ScoreH ?? 0;
hVal.SumScoredH += hVal.ScoreH ?? 0;
this.tDict[hTeam] = hVal;
return(hVal.Points);
}
public static HistoryValue getHistoryVal(SqlDataReader reader, Dictionary<int, RoundValue> rDict, string field)
{
byte round = (byte)reader["Round"];
string team = field == "HOME" ? reader["HomeTeam"].ToString() : reader["AwayTeam"].ToString();
HistoryValue hVal;
if (round == 1) hVal = new HistoryValue();
else if (rDict[round - 1].tDict.ContainsKey(team))
hVal = rDict[round - 1].tDict[team];
else
{
History.errDict[new ArchiveKey(reader)] = String.Format(" BAD Fixture!!! '{0}' has not played in round {1}.", team, round - 1);
hVal = new HistoryValue();
}
hVal.ScoreA = reader["ScoreA"] is DBNull ? null : (byte?)reader["ScoreA"];
hVal.ScoreH = reader["ScoreH"] is DBNull ? null : (byte?)reader["ScoreH"];
hVal.Field = field;
hVal.Date = (DateTime)reader["Date"];
return hVal;
}
protected int insert(ArchiveKey aKey, int round, string team, HistoryValue hVal)
{
string q = @"SELECT ID FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season AND Round=@Round AND Team=@Team";
SqlCommand selectCmd = new SqlCommand(q, Connection);
selectCmd.Parameters.AddWithValue("@Country", aKey.Country);
selectCmd.Parameters.AddWithValue("@League", aKey.League);
selectCmd.Parameters.AddWithValue("@Season", aKey.Season);
selectCmd.Parameters.AddWithValue("@Round", round);
selectCmd.Parameters.AddWithValue("@Team", team);
SqlCommand modifyCmd;
using (SqlDataReader reader = selectCmd.ExecuteReader())
{
if (reader.HasRows)
{
q = @"UPDATE dbo.history SET
Country=@Country, League=@League, Season=@Season, Round=@Round, Date=@Date, Field=@Field, Team=@Team,
ScoredH=@ScoredH, ReceivedH=@ReceivedH, ScoredA=@ScoredA, ReceivedA=@ReceivedA, Scored=@Scored, Received=@Received,
Points=@Points, Position=@Position, grLevel=@grLevel, grStrength=@grStrength, OtherTeam=@OtherTeam
WHERE ID=@ID";
modifyCmd = new SqlCommand(q, Connection);
reader.Read();
modifyCmd.Parameters.AddWithValue("@ID", reader[0]);
}
else
{
q = @"INSERT INTO dbo.history (
Country,League,Season,Round,Date,Field,Team,ScoredH,ReceivedH,ScoredA,ReceivedA,Scored,Received,Points,Position,grLevel,grStrength,OtherTeam
) VALUES(
@Country,@League,@Season,@Round,@Date,@Field,@Team,@ScoredH,@ReceivedH,@ScoredA,@ReceivedA,@Scored,@Received,@Points,@Position,@grLevel,@grStrength,@OtherTeam
)";
modifyCmd = new SqlCommand(q, Connection);
}
}
modifyCmd.Parameters.AddWithValue("@Country", aKey.Country);
modifyCmd.Parameters.AddWithValue("@League", aKey.League);
modifyCmd.Parameters.AddWithValue("@Season", aKey.Season);
modifyCmd.Parameters.AddWithValue("@Round", round);
modifyCmd.Parameters.AddWithValue("@Date", hVal.Date);
modifyCmd.Parameters.AddWithValue("@Team", team);
modifyCmd.Parameters.AddWithValue("@Field", hVal.Field);
modifyCmd.Parameters.AddWithValue("@ScoredH", hVal.SumScoredH);
modifyCmd.Parameters.AddWithValue("@ReceivedH", hVal.SumReceivedH);
modifyCmd.Parameters.AddWithValue("@ScoredA", hVal.SumScoredA);
modifyCmd.Parameters.AddWithValue("@ReceivedA", hVal.SumReceivedA);
modifyCmd.Parameters.AddWithValue("@Scored", hVal.SumScored);
modifyCmd.Parameters.AddWithValue("@Received", hVal.SumReceived);
modifyCmd.Parameters.AddWithValue("@Points", hVal.Points);
modifyCmd.Parameters.AddWithValue("@Position", hVal.Position);
modifyCmd.Parameters.AddWithValue("@grLevel", hVal.grLevel);
modifyCmd.Parameters.AddWithValue("@grStrength", (object)hVal.grStrength ?? DBNull.Value);
modifyCmd.Parameters.AddWithValue("@OtherTeam", hVal.OtherTeam);
return modifyCmd.ExecuteNonQuery();
}
