//Load Specific Game From Database
public Game LoadGame(DateTime airdate)
{
Board rnd1, rnd2;
Clue FJ_Clue;
String FJ_Cat;
string query = "SELECT * FROM Clues WHERE Airdate = @Airdate";
using (connection = new SqlConnection(connectionString.ConnectionString))
using (command = new SqlCommand(query, connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
//Category[] categories = new Category[6];
command.Parameters.Add("@Airdate", SqlDbType.SmallDateTime).Value = airdate.ToLongDateString();
DataTable roundClues = new DataTable();
adapter.Fill(roundClues);
//Split Results By Round
List <DataTable> Rounds = roundClues.AsEnumerable()
.GroupBy(row => row.Field <int>("Round"))
.Select(g => g.CopyToDataTable())
.ToList();
//Parse Round 1 and 2 into Board
if (Rounds.Count < 2)
{
//IF EMPTY HTML PAGE (e.g. http://www.j-archive.com/showgame.php?game_id=320)
return(new Game());
}
rnd1 = LoadRound(Rounds[0]);
rnd2 = LoadRound(Rounds[1]);
//Parse Rnd3 into a Clue
if (Rounds.Count == 3)
{
FJ_Clue = rowToClue(Rounds[2].Rows[0]);
FJ_Cat = Rounds[2].Rows[0]["Category"].ToString();
}
else
{//no FINAL
FJ_Clue = new Clue();
FJ_Cat = "NO FINAL JEOPARDY";
}
}
return(new Game(rnd1, rnd2, FJ_Clue, FJ_Cat, airdate.ToLongDateString()));
}
//Insert into Database
private void InsertClue(Clue c, string category, int rnd, string airdate)
{
if (c.Clue_Text == "" && c.Clue_Answer == "")
{
return;
}
string query = "INSERT INTO dbo.Clues (Text,Answer,Value,Category,DailyDouble,Round,Airdate) VALUES (@Text, @Answer,@Value,@Category,@DailyDouble,@Round,@Airdate)";
using (connection = new SqlConnection(connectionString.ConnectionString))
using (command = new SqlCommand(query, connection))
{
command.Connection.Open();
command.Parameters.Add("@Text", SqlDbType.NVarChar).Value = c.Clue_Text;
command.Parameters.Add("@Answer", SqlDbType.NVarChar).Value = c.Clue_Answer;
command.Parameters.Add("@Value", SqlDbType.Int).Value = c.Clue_Value;
command.Parameters.Add("@Category", SqlDbType.NVarChar).Value = category;
command.Parameters.Add("@DailyDouble", SqlDbType.Bit).Value = c.daily_double;
command.Parameters.Add("@Round", SqlDbType.Int).Value = rnd;
command.Parameters.Add("@Airdate", SqlDbType.SmallDateTime).Value = airdate;
command.ExecuteNonQuery();
}
}
public Game Parse_Page(int gameid, bool online = true)
{
Game parsed_game;
Board rnd1, rnd2;
Clue final_round_clue;
string airdate;
HtmlDocument game_html;
string path = "downloaded_games//" + gameid + ".html";
//Parse HTML and return a Game Object
if (!System.IO.File.Exists(path))
{
string url = "http://j-archive.com/showgame.php?game_id=" + gameid;
HtmlWeb htmlWeb = new HtmlWeb();
game_html = htmlWeb.Load(url);
}
else
{
game_html = new HtmlDocument();
game_html.Load(path);
}
string date_pattern = "[0-9]{4}-[0-9]{2}-[0-9]{2}";
Regex datere = new Regex(date_pattern);
string date = game_html.DocumentNode.SelectSingleNode("/html/head/title").InnerText;
airdate = datere.Match(date).Groups[0].Value;
//Get First Round Board
HtmlNode first_round = game_html.DocumentNode.SelectSingleNode("/html/body/div[@id='content']/div[@id='jeopardy_round']");
if (first_round != null)
{
rnd1 = Parse_Round(first_round, 1);
}
else
{
return(new Game());
//throw new System.NullReferenceException();
};
//Second Round Board
HtmlNode second_round = game_html.GetElementbyId("double_jeopardy_round");
if (second_round != null)
{
rnd2 = Parse_Round(second_round, 2);
}
else
{
return(new Game());
//throw new System.NullReferenceException();
};
//Final Jeopardy Clue
HtmlNode final_round = game_html.DocumentNode.SelectSingleNode("/html/body/div[@id='content']/div[@id='final_jeopardy_round']");
//Final Jeopardy Category
if (final_round == null)
{
return(new Game(rnd1, rnd2, new Clue(), "", airdate));
}
string FJ_Clue_Category = final_round.SelectSingleNode("./table/*").InnerText.Trim();
HtmlNodeCollection FJ_Clue = final_round.SelectNodes("//*[@id='clue_FJ']");
//Final Jeopardy Clue Text
string FJ_Text = final_round.SelectSingleNode("//*[@id='clue_FJ']").InnerText;
//Final Jeopardy Parse answer
HtmlNode answer_node = final_round.SelectSingleNode("./table/tr/td/div");
string answer_html = WebUtility.HtmlDecode(answer_node.Attributes["onmouseover"].Value);
string FJ_Answer = Parse_Answer_Html(answer_html);
//Final Jeopardy Clue Object
final_round_clue = new Clue(FJ_Text, FJ_Answer, -1, true);
parsed_game = new Game(rnd1, rnd2, final_round_clue, FJ_Clue_Category, airdate);
return(parsed_game);
}
private Board Parse_Round(HtmlNode round_html, int rnd)
{
//board contains 5 rows and 6 columns of clues
Clue[,] board_clues = new Clue[5, 6];
Category[] board_categories = new Category[6];
string Clue_Answer;
string Clue_Text;
int[] round_values;
if (rnd == 1)
{
round_values = new int[] { 200, 400, 600, 800, 1000 };
}
else
{
round_values = new int[] { 400, 800, 1200, 1600, 2000 };
}
//Categories
HtmlNodeCollection round_categories = round_html.SelectNodes("./table[@class='round']/tr/td[@class='category']");
//Clues
HtmlNodeCollection round_clues = round_html.SelectNodes("./table[@class='round']/tr/td[@class='clue']");;
int col = 0, row = 0;
int row_value;
foreach (HtmlNode clue in round_clues)
{
row_value = round_values[row];
//process clues
int clues_processed = 0;
//Implement missing clues behavior;
if (clue.InnerHtml != "\n ")
{
//Clue Text
Clue_Text = clue.SelectSingleNode("./table/tr/td[@class='clue_text']").InnerText;
//Parse answer
string answer_html = clue.SelectSingleNode("./table/tr/td/div").OuterHtml;
Clue_Answer = Parse_Answer_Html(answer_html);
}
else
{
Clue_Text = "";
Clue_Answer = "";
}
//Maybe just don't parse clue values and have defaults
Clue newClue = new Clue(Clue_Text, Clue_Answer, row_value, false);
//Store into ClueArray at row,col
board_clues[row, col] = newClue;
//int Clue_Value = Int32.Parse(clue.SelectSingleNode("./tr/td/div/table/tr/td[@class='clue_value']").InnerText.Trim().TrimStart('$'));
col++;
if (col % 6 == 0)
{
col = 0;
row++;
}
clues_processed++;
}
//At this point we have categories and clues we need to create board
col = 0;
row = 0;
//Create Category
foreach (HtmlNode category in round_categories)
{
string category_title = category.InnerText.Trim();
Clue[] category_clues = new Clue[5];
for (int i = 0; i < 5; i++)
{
category_clues[i] = board_clues[i, col];
}
Category cat = new Category(category_title, category_clues);
col++;
if (col % 6 == 0)
{
col = 0;
row++;
}
board_categories[col] = cat;
}
return(new Board(rnd, board_categories));
}