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());
}
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();
}
public void process()
{
byte i, round;
float grDiff;
int aI = 1, progress;
string progressTitle;
string q = @"SELECT Country,League,Season,Round,Date,HomeTeam,AwayTeam,ScoreH,ScoreA FROM dbo.archive ORDER BY Country,League,Season,Round";
ArchiveKey aKey = new ArchiveKey();
Dictionary<ArchiveKey, Dictionary<int, RoundValue>> aDict = new Dictionary<ArchiveKey, Dictionary<int, RoundValue>>();
Dictionary<ArchiveKey, Dictionary<int, RoundValue>> newADict = new Dictionary<ArchiveKey, Dictionary<int, RoundValue>>();
HistoryValue hVal;
RoundValue rVal;
SqlCommand selectCmd;
SqlDataReader reader;
mainForm.backgroundWorker.ReportProgress(0, new BGReport(" Building aDict ..."));
History.errDict = new Dictionary<ArchiveKey, string>();
using (reader = new SqlCommand(q, this.Connection).ExecuteReader())
{
while (reader.Read())
{
aKey.Country = reader["Country"].ToString();
aKey.League = reader["League"].ToString();
aKey.Season = reader["Season"].ToString();
round = (byte)reader["Round"];
if (!aDict.ContainsKey(aKey))
aDict[aKey] = new Dictionary<int, RoundValue>();
else if (aDict[aKey].ContainsKey(round))
{
rVal = aDict[aKey][round];
rVal.calc(reader, aDict[aKey]);
aDict[aKey][round] = rVal;
continue;
}
aDict[aKey][round] = new RoundValue(reader, aDict[aKey]);
}
}
mainForm.backgroundWorker.ReportProgress(10, new BGReport(" Inserting/Updating records ..."));
foreach (var aKVP in aDict)
{
newADict[aKVP.Key] = new Dictionary<int, RoundValue>();
selectCmd = new SqlCommand("SELECT TOP 1 Country FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season", this.Connection);
selectCmd.Parameters.AddWithValue("@Country", aKVP.Key.Country);
selectCmd.Parameters.AddWithValue("@League", aKVP.Key.League);
selectCmd.Parameters.AddWithValue("@Season", aKVP.Key.Season);
using (reader = selectCmd.ExecuteReader()) progressTitle = reader.HasRows ? " Updating " : " Inserting ";
progress = (int)((double)(aI++) / aDict.Count * 80) + 10;
mainForm.backgroundWorker.ReportProgress(progress, new BGReport(progressTitle + aKVP.Key + " ..."));
if (History.errDict.ContainsKey(aKVP.Key))
mainForm.backgroundWorker.ReportProgress(progress, new BGReport(History.errDict[aKVP.Key], System.Drawing.Brushes.Red));
foreach (var rKVP in aKVP.Value)
{
var orderedEnum = from item in rKVP.Value.tDict
let diff = item.Value.SumScored - item.Value.SumReceived
orderby item.Value.Points descending, diff descending
select item;
rVal = new RoundValue(rKVP.Value);
grDiff = (float)(rVal.maxPoints - rVal.minPoints) / 4;
i = 1;
foreach (var kvp in orderedEnum)
{
hVal = kvp.Value;
hVal.Position = i++;
hVal.grLevel = (byte)(Math.Round((rVal.maxPoints - hVal.Points) / grDiff) + 1);
if (rKVP.Key == 1) hVal.grStrength = 50;
else
{
//hVal.grStrength = getStrength1(newADict[aKVP.Key][rKVP.Key - 1], kvp);
//Console.Write("{0} -> {1}). ", rKVP.Key - 1, rKVP.Key);
hVal.grStrength = getStrength2(newADict[aKVP.Key][rKVP.Key - 1], kvp);
//Console.WriteLine(", PrevAvgStrength:{0}", hVal.grStrength);
}
if (insert(aKVP.Key, rKVP.Key, kvp.Key, hVal) == 0) Console.WriteLine("something is wrong [History.process]");
//Console.WriteLine("{0}:{1}, {2}, P:{3}({4},{5})", aKVP.Key, rKVP.Key, kvp.Key, hVal.Points, hVal.grLevel, hVal.grStrength);
rVal.tDict[kvp.Key] = hVal;
}
newADict[aKVP.Key][rKVP.Key] = rVal;
//Console.WriteLine("-- {0} --\n", rKVP.Value);
}
}
}
public void process()
{
byte i, round;
float grDiff;
int aI = 1, progress;
string progressTitle;
string q = @"SELECT Country,League,Season,Round,Date,HomeTeam,AwayTeam,ScoreH,ScoreA FROM dbo.archive ORDER BY Country,League,Season,Round";
ArchiveKey aKey = new ArchiveKey();
Dictionary <ArchiveKey, Dictionary <int, RoundValue> > aDict = new Dictionary <ArchiveKey, Dictionary <int, RoundValue> >();
Dictionary <ArchiveKey, Dictionary <int, RoundValue> > newADict = new Dictionary <ArchiveKey, Dictionary <int, RoundValue> >();
HistoryValue hVal;
RoundValue rVal;
SqlCommand selectCmd;
SqlDataReader reader;
mainForm.backgroundWorker.ReportProgress(0, new BGReport(" Building aDict ..."));
History.errDict = new Dictionary <ArchiveKey, string>();
using (reader = new SqlCommand(q, this.Connection).ExecuteReader())
{
while (reader.Read())
{
aKey.Country = reader["Country"].ToString();
aKey.League = reader["League"].ToString();
aKey.Season = reader["Season"].ToString();
round = (byte)reader["Round"];
if (!aDict.ContainsKey(aKey))
{
aDict[aKey] = new Dictionary <int, RoundValue>();
}
else if (aDict[aKey].ContainsKey(round))
{
rVal = aDict[aKey][round];
rVal.calc(reader, aDict[aKey]);
aDict[aKey][round] = rVal;
continue;
}
aDict[aKey][round] = new RoundValue(reader, aDict[aKey]);
}
}
mainForm.backgroundWorker.ReportProgress(10, new BGReport(" Inserting/Updating records ..."));
foreach (var aKVP in aDict)
{
newADict[aKVP.Key] = new Dictionary <int, RoundValue>();
selectCmd = new SqlCommand("SELECT TOP 1 Country FROM dbo.history WHERE Country=@Country AND League=@League AND Season=@Season", this.Connection);
selectCmd.Parameters.AddWithValue("@Country", aKVP.Key.Country);
selectCmd.Parameters.AddWithValue("@League", aKVP.Key.League);
selectCmd.Parameters.AddWithValue("@Season", aKVP.Key.Season);
using (reader = selectCmd.ExecuteReader()) progressTitle = reader.HasRows ? " Updating " : " Inserting ";
progress = (int)((double)(aI++) / aDict.Count * 80) + 10;
mainForm.backgroundWorker.ReportProgress(progress, new BGReport(progressTitle + aKVP.Key + " ..."));
if (History.errDict.ContainsKey(aKVP.Key))
{
mainForm.backgroundWorker.ReportProgress(progress, new BGReport(History.errDict[aKVP.Key], System.Drawing.Brushes.Red));
}
foreach (var rKVP in aKVP.Value)
{
var orderedEnum = from item in rKVP.Value.tDict
let diff = item.Value.SumScored - item.Value.SumReceived
orderby item.Value.Points descending, diff descending
select item;
rVal = new RoundValue(rKVP.Value);
grDiff = (float)(rVal.maxPoints - rVal.minPoints) / 4;
i = 1;
foreach (var kvp in orderedEnum)
{
hVal = kvp.Value;
hVal.Position = i++;
hVal.grLevel = (byte)(Math.Round((rVal.maxPoints - hVal.Points) / grDiff) + 1);
if (rKVP.Key == 1)
{
hVal.grStrength = 50;
}
else
{
//hVal.grStrength = getStrength1(newADict[aKVP.Key][rKVP.Key - 1], kvp);
//Console.Write("{0} -> {1}). ", rKVP.Key - 1, rKVP.Key);
hVal.grStrength = getStrength2(newADict[aKVP.Key][rKVP.Key - 1], kvp);
//Console.WriteLine(", PrevAvgStrength:{0}", hVal.grStrength);
}
if (insert(aKVP.Key, rKVP.Key, kvp.Key, hVal) == 0)
{
Console.WriteLine("something is wrong [History.process]");
}
//Console.WriteLine("{0}:{1}, {2}, P:{3}({4},{5})", aKVP.Key, rKVP.Key, kvp.Key, hVal.Points, hVal.grLevel, hVal.grStrength);
rVal.tDict[kvp.Key] = hVal;
}
newADict[aKVP.Key][rKVP.Key] = rVal;
//Console.WriteLine("-- {0} --\n", rKVP.Value);
}
}
}