public void InsertYucerawdata(Yucerawdata data)
{
int total = 0;
string conStr = "server=localhost;User Id=root;database=aicai;" +
"Password=root;Character Set=utf8;";
using (MySqlConnection mySqlCon = new MySqlConnection(conStr))
{
mySqlCon.Open();
int recordCount = 0;
using (MySqlCommand myCountCom = mySqlCon.CreateCommand())
{
//有可能存在p2没有数据的情况，这时候只需要通过url进行比较就对了
if (string.IsNullOrEmpty(data.P2))
{
string sqlCountStr = @"
select count(*) from yuce_rawdata where url = ?url;";
myCountCom.CommandText = sqlCountStr;
myCountCom.Parameters.AddWithValue("?url", data.Url);
object obj = myCountCom.ExecuteScalar();
recordCount = Convert.ToInt32(obj);
}
else
{
string sqlCountStr = @"
select count(*) from yuce_rawdata where url = ?url and p2 = ?p2;";
myCountCom.CommandText = sqlCountStr;
myCountCom.Parameters.AddWithValue("?url", data.Url);
myCountCom.Parameters.AddWithValue("?p2", data.P2);
object obj = myCountCom.ExecuteScalar();
recordCount = Convert.ToInt32(obj);
}
}
if (recordCount == 0)
{
using (MySqlCommand mySqlCom = mySqlCon.CreateCommand())
{
//这里需要注意的是在 Sql 语句中有一个参数是 ?BlogsName
//很明显，这个参数有点古怪，和我们一直使用的 @ 不一样，
//这是因为在 MySql 以前的提供的 .NET Connector 中
//都是采用 ? 来标志一个参数的，而现在的 .NET Connector 呢，
//您既可以采用 ? 来标志一个参数，您也可以使用一个 @ 符号来标志一个参数
//新版的 .NET Connector 对此都是支持的
string sqlStr = @"
insert into yuce_rawdata(caizhongtype,publishdate,bisaishijian,title,url,p1,p2,p3,p4,p5,p6,p7,p8,operator,operatetime)
values(?caizhongtype,?publishdate,?bisaishijian,?title,?url,?p1,?p2,?p3,?p4,?p5,?p6,?p7,?p8,?operator,?operatetime)
";
mySqlCom.CommandText = sqlStr;
mySqlCom.Parameters.AddWithValue("?caizhongtype", data.Caizhongtype);
mySqlCom.Parameters.AddWithValue("?publishdate", data.Publishdate);
mySqlCom.Parameters.AddWithValue("?bisaishijian", data.Bisaishijian);
mySqlCom.Parameters.AddWithValue("?title", data.Title);
mySqlCom.Parameters.AddWithValue("?url", data.Url);
mySqlCom.Parameters.AddWithValue("?p1", data.P1);
mySqlCom.Parameters.AddWithValue("?p2", data.P2);
mySqlCom.Parameters.AddWithValue("?p3", data.P3);
mySqlCom.Parameters.AddWithValue("?p4", data.P4);
mySqlCom.Parameters.AddWithValue("?p5", data.P5);
mySqlCom.Parameters.AddWithValue("?p6", data.P6);
mySqlCom.Parameters.AddWithValue("?p7", data.P7);
mySqlCom.Parameters.AddWithValue("?p8", data.P8);
mySqlCom.Parameters.AddWithValue("?operator", data.Operator);
mySqlCom.Parameters.AddWithValue("?operatetime", data.OperateTime);
mySqlCom.ExecuteScalar();
}
}
}
}
private void SaveYuceDetail(string caizhongtype, string url, string title, DateTime publishdate)
{
string urlTemplate = @"http://www.310win.com/{0}";
url = string.Format(urlTemplate, url);
string htmlData = GetHtmlFromUrl(url);
HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument();
document.LoadHtml(htmlData);
HtmlNode rootNode = document.DocumentNode;
HtmlNodeCollection articleContent = rootNode.SelectNodes(@"//div[@class='articleContent']");
HtmlNodeCollection p = articleContent[0].SelectNodes(@"./p");
//Console.WriteLine(p.Count);
Yucerawdata data = null;
bool alreadyTuijian = false;
foreach (HtmlNode pChild in p)
{
string pText = pChild.InnerText.Trim();
if (pText.IndexOf("VS") != -1 || pText.IndexOf("vs") != -1)
{
//如果data没有初始化，说明没有添加信息
if (data == null)
{
data = new Yucerawdata();
data.OperateTime = DateTime.Now;
data.Operator = "吴林";
data.Title = title;
data.Url = url;
data.Publishdate = publishdate;
data.Caizhongtype = caizhongtype;
data.P2 = pText;
HtmlNode lastP = GetSiblingPNode(pChild);
if (lastP != null)
{
data.P1 = lastP.InnerText.Trim();
}
else
{
data.P1 = "";
}
}
else
{
//添加到数据库，并重设为初始值
new YucerawdataDAL().InsertYucerawdata(data);
alreadyTuijian = false;
data = new Yucerawdata();
data.OperateTime = DateTime.Now;
data.Operator = "吴林";
data.Title = title;
data.Url = url;
data.Publishdate = publishdate;
data.Caizhongtype = caizhongtype;
data.P2 = pText;
HtmlNode lastP = GetSiblingPNode(pChild);
if (lastP != null)
{
data.P1 = lastP.InnerText.Trim();
}
else
{
data.P1 = "";
}
}
//int vsIndex = pText.IndexOf("VS");
//string zhudui = pText.Substring(0, vsIndex).Trim();
//string kedui = pText.Substring(vsIndex + 2).Trim();
//Console.WriteLine(zhudui);
//Console.WriteLine(kedui);
}
if (pText.IndexOf("开赛时间") != -1)
{
Console.WriteLine(pText);
string[] innerStrings = pText.Split('\t');
data.P3 = innerStrings[0].Replace("开赛时间", "比赛时间");
if (innerStrings.Length > 1)
{
data.P4 = innerStrings[1].Replace(" ", "");
data.P5 = innerStrings[2].Replace(" ", "");
}
if (innerStrings.Length >= 4)
{
data.P8 = innerStrings[3].Replace(" ", "");
}
if (innerStrings.Length >= 5)
{
data.P9 = innerStrings[4].Replace(" ", "");
}
alreadyTuijian = true;
}
else
{
if (pText.IndexOf("比赛时间") != -1)
{
//如果这里为空，说明之前没有取到VS，继续处理没有意义，先忽略掉
if (data == null)
{
data = new Yucerawdata();
data.OperateTime = DateTime.Now;
data.Operator = "吴林";
data.Title = title;
data.Url = url;
data.Publishdate = publishdate;
data.Caizhongtype = caizhongtype;
data.P2 = pText;
break;
}
data.P3 = pText;
//int bisaiIndex = pText.Trim().IndexOf("比赛时间");
//string bisaishijian = pText.Trim().Substring(5);
//Console.WriteLine(bisaishijian);
}
if (pText.IndexOf("欧赔") != -1)
{
data.P4 = pText;
}
if (pText.IndexOf("盘口") != -1)
{
data.P5 = pText;
}
if (pText.IndexOf("赛事分析") != -1)
{
data.P6 = pText;
}
if (pText.IndexOf("盘赔数据分析") != -1)
{
data.P7 = pText;
}
if (pText.IndexOf("推荐") != -1 &&
(pText.IndexOf("竞彩") != -1 || pText.IndexOf("北京单场") != -1 || pText.IndexOf("北单") != -1))
{
if (!alreadyTuijian)
{
data.P8 = pText;
alreadyTuijian = true;
}
}
}
}
if (data != null)
{
new YucerawdataDAL().InsertYucerawdata(data);
}
}
private void btnImportCaikeDetail_Click(object sender, EventArgs e)
{
HtmlAgilityPack.HtmlDocument document = new HtmlAgilityPack.HtmlDocument();
document.Load("html/彩客咨询明细4.html", Encoding.UTF8);
HtmlNode rootNode = document.DocumentNode;
HtmlNodeCollection articleContent = rootNode.SelectNodes(@"//div[@class='articleContent']");
HtmlNodeCollection p = articleContent[0].SelectNodes(@"./p");
//Console.WriteLine(p.Count);
Yucerawdata data = null;
foreach (HtmlNode pChild in p)
{
string pText = pChild.InnerText.Trim();
if (pText.IndexOf("VS")!= -1 || pText.IndexOf("vs")!= -1)
{
//如果data没有初始化，说明没有添加信息
if (data == null)
{
data = new Yucerawdata();
data.OperateTime = DateTime.Now;
data.Operator = "吴林";
data.P2 = pText;
data.P1 = pChild.PreviousSibling.PreviousSibling.InnerText.Trim();
}
else
{
//添加到数据库，并设为null
new YucerawdataDAL().InsertYucerawdata(data);
data = new Yucerawdata();
data.OperateTime = DateTime.Now;
data.Operator = "吴林";
data.P2 = pText;
data.P1 = pChild.PreviousSibling.PreviousSibling.InnerText.Trim();
}
int vsIndex = pText.IndexOf("VS");
string zhudui = pText.Substring(0, vsIndex).Trim();
string kedui = pText.Substring(vsIndex+2).Trim();
Console.WriteLine(zhudui);
Console.WriteLine(kedui);
}
if (pText.IndexOf("比赛时间") != -1)
{
data.P3 = pText;
int bisaiIndex = pText.Trim().IndexOf("比赛时间");
string bisaishijian = pText.Trim().Substring(5);
Console.WriteLine(bisaishijian);
}
if (pText.IndexOf("欧赔") != -1)
{
data.P4 = pText;
}
if (pText.IndexOf("盘口") != -1)
{
data.P5 = pText;
}
if (pText.IndexOf("盘口") != -1)
{
data.P5 = pText;
}
if (pText.IndexOf("赛事分析") != -1)
{
data.P6 = pText;
}
if(pText.IndexOf ("盘赔数据分析") != -1)
{
data.P7 = pText;
}
if (pText.IndexOf("推荐") != -1) //&& pText.IndexOf("竞彩") != -1
{
data.P8 = pText;
//int tuijianIndex = pText.Trim().IndexOf("推荐");
int tuijianIndex = pText.Trim().IndexOf("：");
string tuijian = pText.Trim().Substring(tuijianIndex);
Console.WriteLine(tuijian);
}
}
if(data != null)
{
new YucerawdataDAL().InsertYucerawdata(data);
}
}