/// <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 }