/// <summary>
/// 增加一条数据
/// </summary>
public long Add(XCLShouCang.Model.TB_Product model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into TB_Product(");
strSql.Append("FK_SearchKeyID,ID,ProductURL,Sort,ImgURL,Price,Title,MonthDealCount,AppraiseCount,ShopName,ShopID,ShopURL,ShopProvince,ShopCity,ProductProvince,ProductCity,Rate,CreateTime)");
strSql.Append(" values (");
strSql.Append("@FK_SearchKeyID,@ID,@ProductURL,@Sort,@ImgURL,@Price,@Title,@MonthDealCount,@AppraiseCount,@ShopName,@ShopID,@ShopURL,@ShopProvince,@ShopCity,@ProductProvince,@ProductCity,@Rate,@CreateTime)");
strSql.Append(";select SCOPE_IDENTITY()");
SqlParameter[] parameters =
{
new SqlParameter("@FK_SearchKeyID", SqlDbType.BigInt, 8),
new SqlParameter("@ID", SqlDbType.BigInt, 8),
new SqlParameter("@ProductURL", SqlDbType.VarChar, 100),
new SqlParameter("@Sort", SqlDbType.Int, 4),
new SqlParameter("@ImgURL", SqlDbType.VarChar, 1000),
new SqlParameter("@Price", SqlDbType.Decimal, 9),
new SqlParameter("@Title", SqlDbType.VarChar, 200),
new SqlParameter("@MonthDealCount", SqlDbType.Int, 4),
new SqlParameter("@AppraiseCount", SqlDbType.Int, 4),
new SqlParameter("@ShopName", SqlDbType.VarChar, 50),
new SqlParameter("@ShopID", SqlDbType.BigInt, 8),
new SqlParameter("@ShopURL", SqlDbType.VarChar, 100),
new SqlParameter("@ShopProvince", SqlDbType.VarChar, 50),
new SqlParameter("@ShopCity", SqlDbType.VarChar, 50),
new SqlParameter("@ProductProvince", SqlDbType.VarChar, 50),
new SqlParameter("@ProductCity", SqlDbType.VarChar, 50),
new SqlParameter("@Rate", SqlDbType.Decimal, 9),
new SqlParameter("@CreateTime", SqlDbType.DateTime)
};
parameters[0].Value = model.FK_SearchKeyID;
parameters[1].Value = model.ID;
parameters[2].Value = model.ProductURL;
parameters[3].Value = model.Sort;
parameters[4].Value = model.ImgURL;
parameters[5].Value = model.Price;
parameters[6].Value = model.Title;
parameters[7].Value = model.MonthDealCount;
parameters[8].Value = model.AppraiseCount;
parameters[9].Value = model.ShopName;
parameters[10].Value = model.ShopID;
parameters[11].Value = model.ShopURL;
parameters[12].Value = model.ShopProvince;
parameters[13].Value = model.ShopCity;
parameters[14].Value = model.ProductProvince;
parameters[15].Value = model.ProductCity;
parameters[16].Value = model.Rate;
parameters[17].Value = model.CreateTime;
object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt64(obj));
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public XCLShouCang.Model.TB_Product GetModel(long ProductID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top 1 ProductID,FK_SearchKeyID,ID,ProductURL,Sort,ImgURL,Price,Title,MonthDealCount,AppraiseCount,ShopName,ShopID,ShopURL,ShopProvince,ShopCity,ProductProvince,ProductCity,Rate,CreateTime from TB_Product ");
strSql.Append(" where ProductID=@ProductID");
SqlParameter[] parameters =
{
new SqlParameter("@ProductID", SqlDbType.BigInt)
};
parameters[0].Value = ProductID;
XCLShouCang.Model.TB_Product model = new XCLShouCang.Model.TB_Product();
DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
return(DataRowToModel(ds.Tables[0].Rows[0]));
}
else
{
return(null);
}
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public XCLShouCang.Model.TB_Product DataRowToModel(DataRow row)
{
XCLShouCang.Model.TB_Product model = new XCLShouCang.Model.TB_Product();
if (row != null)
{
if (row["ProductID"] != null && row["ProductID"].ToString() != "")
{
model.ProductID = long.Parse(row["ProductID"].ToString());
}
if (row["FK_SearchKeyID"] != null && row["FK_SearchKeyID"].ToString() != "")
{
model.FK_SearchKeyID = long.Parse(row["FK_SearchKeyID"].ToString());
}
if (row["ID"] != null && row["ID"].ToString() != "")
{
model.ID = long.Parse(row["ID"].ToString());
}
if (row["ProductURL"] != null)
{
model.ProductURL = row["ProductURL"].ToString();
}
if (row["Sort"] != null && row["Sort"].ToString() != "")
{
model.Sort = int.Parse(row["Sort"].ToString());
}
if (row["ImgURL"] != null)
{
model.ImgURL = row["ImgURL"].ToString();
}
if (row["Price"] != null && row["Price"].ToString() != "")
{
model.Price = decimal.Parse(row["Price"].ToString());
}
if (row["Title"] != null)
{
model.Title = row["Title"].ToString();
}
if (row["MonthDealCount"] != null && row["MonthDealCount"].ToString() != "")
{
model.MonthDealCount = int.Parse(row["MonthDealCount"].ToString());
}
if (row["AppraiseCount"] != null && row["AppraiseCount"].ToString() != "")
{
model.AppraiseCount = int.Parse(row["AppraiseCount"].ToString());
}
if (row["ShopName"] != null)
{
model.ShopName = row["ShopName"].ToString();
}
if (row["ShopID"] != null && row["ShopID"].ToString() != "")
{
model.ShopID = long.Parse(row["ShopID"].ToString());
}
if (row["ShopURL"] != null)
{
model.ShopURL = row["ShopURL"].ToString();
}
if (row["ShopProvince"] != null)
{
model.ShopProvince = row["ShopProvince"].ToString();
}
if (row["ShopCity"] != null)
{
model.ShopCity = row["ShopCity"].ToString();
}
if (row["ProductProvince"] != null)
{
model.ProductProvince = row["ProductProvince"].ToString();
}
if (row["ProductCity"] != null)
{
model.ProductCity = row["ProductCity"].ToString();
}
if (row["Rate"] != null && row["Rate"].ToString() != "")
{
model.Rate = decimal.Parse(row["Rate"].ToString());
}
if (row["CreateTime"] != null && row["CreateTime"].ToString() != "")
{
model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
}
}
return(model);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(XCLShouCang.Model.TB_Product model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update TB_Product set ");
strSql.Append("FK_SearchKeyID=@FK_SearchKeyID,");
strSql.Append("ID=@ID,");
strSql.Append("ProductURL=@ProductURL,");
strSql.Append("Sort=@Sort,");
strSql.Append("ImgURL=@ImgURL,");
strSql.Append("Price=@Price,");
strSql.Append("Title=@Title,");
strSql.Append("MonthDealCount=@MonthDealCount,");
strSql.Append("AppraiseCount=@AppraiseCount,");
strSql.Append("ShopName=@ShopName,");
strSql.Append("ShopID=@ShopID,");
strSql.Append("ShopURL=@ShopURL,");
strSql.Append("ShopProvince=@ShopProvince,");
strSql.Append("ShopCity=@ShopCity,");
strSql.Append("ProductProvince=@ProductProvince,");
strSql.Append("ProductCity=@ProductCity,");
strSql.Append("Rate=@Rate,");
strSql.Append("CreateTime=@CreateTime");
strSql.Append(" where ProductID=@ProductID");
SqlParameter[] parameters =
{
new SqlParameter("@FK_SearchKeyID", SqlDbType.BigInt, 8),
new SqlParameter("@ID", SqlDbType.BigInt, 8),
new SqlParameter("@ProductURL", SqlDbType.VarChar, 100),
new SqlParameter("@Sort", SqlDbType.Int, 4),
new SqlParameter("@ImgURL", SqlDbType.VarChar, 1000),
new SqlParameter("@Price", SqlDbType.Decimal, 9),
new SqlParameter("@Title", SqlDbType.VarChar, 200),
new SqlParameter("@MonthDealCount", SqlDbType.Int, 4),
new SqlParameter("@AppraiseCount", SqlDbType.Int, 4),
new SqlParameter("@ShopName", SqlDbType.VarChar, 50),
new SqlParameter("@ShopID", SqlDbType.BigInt, 8),
new SqlParameter("@ShopURL", SqlDbType.VarChar, 100),
new SqlParameter("@ShopProvince", SqlDbType.VarChar, 50),
new SqlParameter("@ShopCity", SqlDbType.VarChar, 50),
new SqlParameter("@ProductProvince", SqlDbType.VarChar, 50),
new SqlParameter("@ProductCity", SqlDbType.VarChar, 50),
new SqlParameter("@Rate", SqlDbType.Decimal, 9),
new SqlParameter("@CreateTime", SqlDbType.DateTime),
new SqlParameter("@ProductID", SqlDbType.BigInt, 8)
};
parameters[0].Value = model.FK_SearchKeyID;
parameters[1].Value = model.ID;
parameters[2].Value = model.ProductURL;
parameters[3].Value = model.Sort;
parameters[4].Value = model.ImgURL;
parameters[5].Value = model.Price;
parameters[6].Value = model.Title;
parameters[7].Value = model.MonthDealCount;
parameters[8].Value = model.AppraiseCount;
parameters[9].Value = model.ShopName;
parameters[10].Value = model.ShopID;
parameters[11].Value = model.ShopURL;
parameters[12].Value = model.ShopProvince;
parameters[13].Value = model.ShopCity;
parameters[14].Value = model.ProductProvince;
parameters[15].Value = model.ProductCity;
parameters[16].Value = model.Rate;
parameters[17].Value = model.CreateTime;
parameters[18].Value = model.ProductID;
int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 抓取取宝贝信息
/// </summary>
private void GrabeProductFromPage(XCLShouCang.Model.TB_SearchKey keyModel, Action <string> action_ShowProcess, XCLNetTools.Message.MessageModel msgModel)
{
DateTime dtNow = DateTime.Now;
List <XCLShouCang.Model.TB_Product> productModelList = new List <XCLShouCang.Model.TB_Product>();
XCLShouCang.Model.TB_Product productModel = null;
//抓取url数据
HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
action_ShowProcess("准备获取宝贝信息");
#region 获取列表页数据
string searchUrl = string.Empty;
switch (keyModel.KeyType)
{
case (int)Common.CommonHelper.SearchTypeEnum.天猫搜索列表链接:
searchUrl = keyModel.KeyName;
break;
default:
searchUrl = Web.Common.CommonHelper.WebInfo.TB_ProductSearchListURL.Replace("{kw}", System.Web.HttpUtility.UrlEncode(keyModel.KeyName, System.Text.Encoding.GetEncoding("GB2312")));
break;
}
string productListHTML = Web.Common.CommonHelper.DownLoadHTMLString(searchUrl);
doc.LoadHtml(productListHTML);
Regex numberRegex = new Regex(@"\d+");
HtmlAgilityPack.HtmlNodeCollection productNodes = doc.DocumentNode.SelectNodes("//div[@id='J_ItemList']/div[@class='product'][@data-id]");
if (null != productNodes && productNodes.Count > 0)
{
for (int i = 0; i < productNodes.Count; i++)
{
if (i == 0 || (i + 1) % 5 == 0)
{
action_ShowProcess(string.Format("正在从搜索列表中提取第【{0}】个宝贝基本信息...", i + 1));
}
var productNode = productNodes[i];
var productNodeWrap = productNode.SelectSingleNode("div[@class='product-iWrap']");
productModel = new XCLShouCang.Model.TB_Product();
productModel.CreateTime = dtNow;
productModel.Sort = i + 1;
productModel.ID = XCLNetTools.StringHander.Common.GetLong(null != productNode.Attributes["data-id"] ? productNode.Attributes["data-id"].Value : "");
//图片
var imgNode = productNodeWrap.SelectSingleNode("child::div[@class='productImg-wrap']/a[@class='productImg']/img");
if (null != imgNode)
{
if (null != imgNode.Attributes["data-ks-lazyload"])
{
productModel.ImgURL = imgNode.Attributes["data-ks-lazyload"].Value;
}
else if (null != imgNode.Attributes["src"])
{
productModel.ImgURL = imgNode.Attributes["src"].Value;
}
}
//价格
var priceNode = productNodeWrap.SelectSingleNode("child::p[@class='productPrice']/em");
productModel.Price = XCLNetTools.StringHander.Common.GetDecimal(null != priceNode.Attributes["title"] ? priceNode.Attributes["title"].Value : "");
//标题
var titleNode = productNodeWrap.SelectSingleNode("child::p[@class='productTitle']/a");
if (null == titleNode)
{
titleNode = productNodeWrap.SelectSingleNode("child::div[@class='productTitle productTitle-spu']/a");//电器城
}
productModel.Title = null != titleNode ? titleNode.InnerText : string.Empty;
//店铺
var shopNode = productNodeWrap.SelectSingleNode("child::div[@class='productShop']/a");
productModel.ShopName = shopNode.InnerText;
productModel.ShopID = XCLNetTools.StringHander.Common.GetLong(new Regex(@"(user_number_id=)(\d+)").Match(null != shopNode.Attributes["href"] ? shopNode.Attributes["href"].Value : "").Groups[2].Value);
productModel.ProductURL = Web.Common.CommonHelper.WebInfo.TB_ProductURL.Replace("{ID}", productModel.ID.ToString());
productModel.ShopURL = Web.Common.CommonHelper.WebInfo.TB_ShopURL.Replace("{ShopID}", productModel.ShopID.ToString());
productModelList.Add(productModel);
}
}
#endregion
#region 获取单个宝贝详细数据
Regex regRate = new Regex(@"(""gradeAvg"":)([1-9]\d*\.\d*|0\.\d*[1-9]\d*)");
Regex regAllRateCount = new Regex(@"(""rateTotal"":)([1-9]\d*|0)");
Regex regAreaFilter = new Regex(@"省|市|(\s+)");
Regex regProductArea = new Regex(@"""deliveryAddress"":""(.*)"",""deliverySkuMap""");
if (null != productModelList && productModelList.Count > 0)
{
for (int i = 0; i < productModelList.Count; i++)
{
if (i == 0 || (i + 1) % 5 == 0)
{
action_ShowProcess(string.Format("正在提取第【{0}】个宝贝详情...", i + 1));
}
try
{
var m = productModelList[i];
//doc.LoadHtml(Web.Common.CommonHelper.DownLoadHTMLString(m.ProductURL));
//var currentDocNode = doc.DocumentNode;
////商家所在地
//var sellNode = currentDocNode.SelectSingleNode("//li[@class='locus']");
//var sellNodeAreaDiv = null != sellNode ? sellNode.SelectSingleNode("div") : null;
//string sellAreaText = null != sellNodeAreaDiv ? sellNodeAreaDiv.InnerText : string.Empty;
//if (!string.IsNullOrEmpty(sellAreaText))
//{
// string[] sellAreaArry = regAreaFilter.Replace(sellAreaText, "").Split(',');
// if (sellAreaArry.Length > 1)
// {
// m.ShopProvince = sellAreaArry[0];
// m.ShopCity = sellAreaArry[1];
// }
// else
// {
// m.ShopProvince = sellAreaArry[0];
// m.ShopCity = sellAreaArry[0];
// }
//}
//宝贝评分
//XCLTBTool({"dsr":{"gradeAvg":4.8,"rateTotal":7202}})
string rateHtml = Web.Common.CommonHelper.DownLoadHTMLString(Web.Common.CommonHelper.WebInfo.TB_ProductRateURL.Replace("{ID}", m.ID.ToString()));
m.AppraiseCount = XCLNetTools.StringHander.Common.GetInt(regAllRateCount.Match(rateHtml).Groups[2].Value);
m.Rate = XCLNetTools.StringHander.Common.GetDecimal(regRate.Match(rateHtml).Groups[2].Value);
//宝贝销量
string productDetailsHTML = Web.Common.CommonHelper.DownLoadHTMLString(Web.Common.CommonHelper.WebInfo.TB_ProductDetailURL.Replace("{ID}", m.ID.ToString()));
if (productDetailsHTML.Contains(@"""sellCount"":"))
{
m.MonthDealCount = XCLNetTools.StringHander.Common.GetInt(new Regex(@"(""sellCount"":)(\d+)").Match(productDetailsHTML).Groups[2].Value);
}
//宝贝所在地
if (regProductArea.Match(productDetailsHTML).Success)
{
string areaText = regProductArea.Match(productDetailsHTML).Groups[1].Value;
if (Web.Common.CommonHelper.WebInfo.TB_ProvinceSingle.Exists(k => string.Equals(k, areaText)))
{
m.ProductProvince = areaText;
m.ProductCity = areaText;
}
else
{
m.ProductProvince = Web.Common.CommonHelper.WebInfo.TB_Province.Where(k => areaText.Contains(k)).First();
m.ProductCity = areaText.Substring(m.ProductProvince.Length);
}
}
}
catch (Exception ex)
{
}
}
}
#endregion
action_ShowProcess("宝贝信息已获取完成");
#region 向数据库中保存数据
XCLShouCang.BLL.TB_SearchKey keyBLL = new XCLShouCang.BLL.TB_SearchKey();
long keyID = keyBLL.Add(keyModel, productModelList);
if (keyID > 0)
{
msgModel.IsSuccess = true;
msgModel.CustomObject = new { KeyID = keyID, RecCount = null == productModelList ? 0 : productModelList.Count, UseSecond = (int)(DateTime.Now.Subtract(dtNow).TotalSeconds) };
msgModel.Message = "查询成功！";
}
else
{
msgModel.IsSuccess = false;
msgModel.Message = "查询失败，请重试！";
}
#endregion
}
