/// <summary>
/// 初始化登录接口
/// </summary>
/// <param name="uName"></param>
/// <param name="uPass"></param>
public void InitLogin(string uName, string uPass)
{
try
{
if (_jdLoginer == null)
{
_jdLoginer = new JDLoginer()
{
r = new Random().NextDouble().ToString(),
//"6dc04d7e7f9b42aca76c1e2cce37059e15601070"
//"c7b303dc911d40729338dd10e4fd2238757832600"
eid = "6dc04d7e7f9b42aca76c1e2cce37059e15601070",
//WBIVASC7H6QRBITLOKI63TPLQXFMGD7T4A7P3PZLD6VAYCKL4GVQYJ5OHJCYRXIKSWQUYTCDQ4VUA
//"211f6cb42bee7b250c6a80b5c75b85ca"
//"b8b0d4ab226290696f575c771a2c72bc"
fp = "211f6cb42bee7b250c6a80b5c75b85ca",
loginname = uName,
loginpwd = uPass,
cookies = "",
chkRememberMe = ""
};
}
_loginParams.Clear();
GetLoginUI();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
}
}
/// <summary>
/// 判断某个商品当天是否有相同价格存在
/// </summary>
/// <param name="pid">商品编号</param>
/// <param name="dt1">开始日期</param>
/// <param name="dt2">结束日期</param>
/// <param name="price">价格</param>
/// <returns></returns>
public ProductPriceHistory WarePriceHistoryGetOne(string pid, DateTime dt1, DateTime dt2, double price)
{
OtDB db = GetDb();
try
{
//datetime(PriceDate,'localtime') datetime(PriceDate,'utc')
db.Begin();
//分页查询
//select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
//select * from account limit10,9
string sql = string.Format("select rowid as RID,*,datetime(PriceDate) as PriceTime from ProductPriceHistory " +
"where pid = '{0}' and (PriceDate between '{1}' and '{2}') and Price={3} order by PriceDate", pid, dt1.ToString("s"), dt2.ToString("s"), price);
List <ProductPriceHistory> rtnList = db.Query <ProductPriceHistory>(sql, null);
db.Commit();
if (rtnList.Count > 0)
{
return(rtnList[0]);
}
else
{
return(null);
}
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}
private void btnGet_Click(object sender, EventArgs e)
{
try
{
//pageInfo.Controls.Add(progressPanel1);
//progressPanel1.BringToFront();
//progressPanel1.Show();
string tID = string.IsNullOrEmpty(txtID.Text) ? "1010527324" : txtID.Text.Trim();
SysParams.GatherModel = GatherType.Single;
ProductInfo tmpWare = WareDealer.WareService.GetInstance().GetWareInfoByID(tID);
if (_isEdit)
{
//如果是浏览/编辑状态，历史低价、价格趋势不变
tmpWare.ProductBasePrice = _myProduct.ProductBasePrice;
tmpWare.ProductPriceTrend = _myProduct.ProductPriceTrend;
}
_myProduct = tmpWare;
SetUIControls();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
}
finally
{
//progressPanel1.Hide();
}
}
/// <summary>
/// 更新商品价格及库存
/// </summary>
/// <param name="pid">商品编号</param>
/// <param name="price1">网页价格</param>
/// <param name="price2">手机价格</param>
/// <param name="price3">QQ价格</param>
/// <param name="price4">微信价格</param>
/// <param name="priceSrc">价格来源</param>
/// <param name="stock">库存</param>
public void WareRepositoryUpdate(string pid, double price1, double price2, double price3, double price4, string priceSrc, int stock)
{
string trend = "未知";
if (stock >= 0)
{
trend = WarePriceTrend(pid, price1);
}
OtDB db = GetDb();
try
{
db.Begin();
//价格对比 --> 涨价、持平、走低、未知
string sql = string.Format("update ProductInfo set ProductPrice = {0}, ProductMobilePrice = {1}, ProductQQPrice = {2}, ProductWXPrice = {3}," +
"ProductPriceDate = DateTime('now'), ProductIsSaled={4}, ProductPriceTrend='{5}',ProductPriceType = '{6}' where ProductID = '{7}';",
price1, price2, price3, price4, stock, trend, priceSrc, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
//获取界面元素值
GetUiControlsText();
if (_isEdit)
{
DBHelper.GetInstance().WareUpdate(_myProduct);
}
else
{
if (!DBHelper.GetInstance().WareIsExists(_myProduct.ProductID))
{
DBHelper.GetInstance().WareInsert(_myProduct);
DBHelper.GetInstance().WarePriceInsert(_myProduct.ProductID, _myProduct.ProductPrice);
}
else
{
DBHelper.GetInstance().WareUpdate(_myProduct);
//MessageBox.Show("当前商品已经存在！无法继续保存。", "系统警告");
//return;
}
}
MessageBox.Show("商品保存成功！", "系统提示");
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
MessageBox.Show("商品保存失败！", "系统提示");
}
}
/// <summary>
/// 更新数据库中所有商品信息
/// </summary>
private void UpdateAllWare()
{
OtCom.XLogInfo("开始自动获取所有商品价格及库存情况！");
//获取数据库中所有商品
List <ProductInfo> wares = DBHelper.GetInstance().WareGetAll();
UpdateThread(wares);
}
/// <summary>
/// 获取某个类别商品数据
/// </summary>
/// <param name="typeid"></param>
/// <returns></returns>
public List <ProductInfo> WareGetAll(string typeid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = "";
if (typeid == "UnType") //未分类
{
sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, "
+ "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=1 and (a.ProductType = '' or a.ProductType is null) order by a.CreateTime";
}
else if (typeid == "down") //降价、走低
{
sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
+ "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='走低' and a.BEnable=1 order by a.CreateTime";
}
else if (typeid == "up") //涨价
{
sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
+ "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='涨价' and a.BEnable=1 order by a.CreateTime";
}
else if (typeid == "balance") //持平
{
sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
+ "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductPriceTrend='持平' and a.BEnable=1 order by a.CreateTime";
}
else if (typeid == "focus") //重点关注
{
sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
+ "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.Focus=1 and a.BEnable=1 order by a.CreateTime";
}
else if (typeid == "trash") //回收站
{
sql = "select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' when 3 then '预订' else '无货' end) as ProductStock, "
+ "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.BEnable=0 order by a.CreateTime";
}
else
{
//分页查询
//select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
//select * from account limit10,9
sql = string.Format("select a.*,(case a.ProductIsSaled when -1 then '下柜' when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, "
+ "b.Name as TypeName from ProductInfo a left join ProductType b on a.ProductType = b.TID where a.ProductType = '{0}' and a.BEnable=1 order by a.CreateTime", typeid);
}
List <ProductInfo> rtnList = db.Query <ProductInfo>(sql, null);
db.Commit();
return(rtnList);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}
void _typeManagerTree_DragOver(object sender, DragEventArgs e)
{
if (e.AllowedEffect == DragDropEffects.Move)
{
if (e.Data.GetDataPresent(typeof(System.Collections.Generic.List <Object>)))
{
List <Object> obj = e.Data.GetData(typeof(System.Collections.Generic.List <Object>)) as List <Object>;
if (obj != null)
{
try
{
ProductInfo curWare = obj[0] as ProductInfo;
//string[] dList = e.Data.GetFormats();
if (curWare != null)
{
//获取当前操作节点
var hi = _typeManagerTree.CalcHitInfo(_typeManagerTree.PointToClient(new Point(e.X, e.Y)));
var targetNode = hi.Node;
if (targetNode.Tag != null)
{
DBHelper.GetInstance().WareTypeUpdate(curWare.ProductID, targetNode.Tag.ToString());
DBHelper.GetInstance().WareReloadOne(curWare.ProductID);
OtCom.XLogInfo(targetNode.Tag.ToString());
}
else
{
object nowKey = _typeManagerTree.GetNodeKey(targetNode);
if (nowKey.ToString() == "ALL" || nowKey.ToString() == "JINDONG" || nowKey.ToString() == "UnType" ||
nowKey.ToString() == "down" || nowKey.ToString() == "up" || nowKey.ToString() == "balance" ||
nowKey.ToString() == "focus")
{
MessageBox.Show("系统基础分类不能增加商品！", "系统提示");
return;
}
else if (nowKey.ToString() == "trash")
{
DBHelper.GetInstance().WareDelOne(curWare.ProductID);
OtCom.XLogInfo("trash");
}
}
RefreshGrid(btnSearch.Tag.ToString());
}
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
}
}
}
}
}
/// <summary>
/// 判断商品是否已存在
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public bool WareIsExists(string pid)
{
try
{
ProductInfo rtnobj = WareGetOne(pid);
return(rtnobj != null);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
return(false);
}
}
/// <summary>
/// 获取京东商品分类信息
/// </summary>
public void GetWareTypes()
{
try
{
//确定采集模式为批量采集
SysParams.GatherModel = GatherType.Batch;
ShowAutoMessage("清空京东商品分类数据...");
//清空京东商品分类数据
DBHelper.GetInstance().WareJDTypeClear();
//<a href="http://channel.jd.com/737-752.html" target="_blank">厨房小电<i>></i></a>
//<a href="http://list.jd.com/list.html?cat=9987,653,655" target="_blank">手机</a>
//<a href="http://list.jd.com/list.html?cat=737,794,798" target="_blank">平板电视</a>
string url = "http://dc.3.cn/category/get";
string rtnHtml = HttpHelper.GetResponseGBK(url, "get", string.Empty);
if (!string.IsNullOrEmpty(rtnHtml))
{
JDWareTypes jdTypes = JsonConvert.DeserializeObject <JDWareTypes>(rtnHtml);
List <JDWareType> _types = new List <JDWareType>();
if (jdTypes.data != null && jdTypes.data.Count > 0)
{
ShowAutoMessage("解析京东商品分类数据...");
foreach (var item in jdTypes.data)
{
if (item.s != null && item.s.Count > 0)
//if (item.s != null && item.s.Count > 0 && item.id == "a")
{
List <JDWareType> rtnList = ParseSubType(item.s, 0);
if (rtnList != null && rtnList.Count > 0)
{
_types.AddRange(rtnList);
}
}
}
}
if (_types.Count > 0)
{
ShowAutoMessage("京东商品分类数据入库...");
DBHelper.GetInstance().WareJDTypeAdd(_types);
ShowAutoMessage("京东商品分类数据入库完成");
}
}
}
catch (Exception ex)
{
ShowAutoMessage(ex.Message);
OtCom.XLogErr(ex.Message);
}
}
/// <summary>
/// 增加商品类型
/// </summary>
/// <param name="wType"></param>
public void WareTypeInsert(ProductType wType)
{
OtDB db = GetDb();
try
{
db.Begin();
db.ExecInsert(wType, "ProductType", new string[] { "TID", "Name", "Description", "BEnable", "CreateTime" });
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 清空京东商品类别数据
/// </summary>
public void WareJDTypeClear()
{
OtDB db = GetDb();
try
{
db.Begin();
db.Exec("delete from JDWareType");
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 清除商品评价信息
/// </summary>
/// <param name="pid"></param>
private void WareMessageClear(string pid)
{
OtDB db = GetDb();
try
{
db.Begin();
db.Exec(string.Format("delete from ProductMessage where PID = '{0}'", pid));
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 清空商品库
/// </summary>
public void WareClear()
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("delete from ProductInfo");
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 逻辑删除/启用商品
/// </summary>
/// <param name="pid"></param>
public void WareReloadOne(string pid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set BEnable = 1 where productid ='{0}'", pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
public void WarePromotionUpdate(string pid, string tag, string promo, string copon)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set ProductTag = '{0}',ProductPromoMsg = '{1}',ProductCoupon = '{2}' where ProductID = '{3}';", tag, promo, copon, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新商品信息
/// </summary>
/// <param name="_myProduct"></param>
public void WareUpdate(ProductInfo _myProduct)
{
OtDB db = GetDb();
try
{
db.Begin();
db.ExecUpdate(_myProduct, "ProductInfo", new string[] { "ProductID", "ProductName", "ProductPrice", "ProductIsSaled",
"ProductTag", "ProductBrand", "ProductAttach", "ProductType", "ProductImagePath" }, string.Format(" ProductID = '{0}'", _myProduct.ProductID), null);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新商品海报数据
/// </summary>
/// <param name="pid"></param>
/// <param name="imgPath"></param>
public void WareImageUpdate(string pid, string imgPath, string imgWebPath)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set ProductImagePath = '{0}',ProductImageWebPath = '{1}' where ProductID = '{2}';", imgPath, imgWebPath, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新产品类型
/// </summary>
/// <param name="wType"></param>
public void WareTypeUpdate(ProductType wType)
{
OtDB db = GetDb();
try
{
db.Begin();
db.ExecUpdate(wType, "ProductType", new string[] { "Name", "Description", "BEnable" },
string.Format(" TID = '{0}'", wType.TID), null);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 删除单个商品
/// </summary>
/// <param name="rid"></param>
public void WareDelOne(string rid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("delete from ProductInfo where rid ='{0}'", rid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新商品归属
/// </summary>
/// <param name="pid">编号</param>
/// <param name="vid">商店编号</param>
/// <param name="sid">厂商编号</param>
/// <param name="cat">配送区域</param>
public void WareShopUpdate(string pid, string vid, string sid, string cat)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set VenderId = '{0}',ShopID = '{1}',Catalog = '{2}' where ProductID = '{3}';", vid, sid, cat, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
public void GetMinPrices()
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select pid, min(price) minprice from ProductPriceHistory group by pid");
//List<ProductPriceHistory> rtnList = db.Query<ProductPriceHistory>(sql, null);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 删除商品价格
/// </summary>
/// <param name="id">商品序号</param>
public void WarePriceDel(Int64 id)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("delete from ProductPriceHistory where rowid = {0}", id);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新商品价格
/// </summary>
/// <param name="pid">商品编号</param>
/// <param name="price">网页价格</param>
public void WarePriceUpdate(string pid, double price)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set ProductPrice = {0},ProductPriceDate = DateTime('now') where ProductID = '{1}';", price, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新产品属性类型
/// </summary>
/// <param name="pid"></param>
public void WareTypeUpdate(string pid, string typeid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set ProductType = '{1}' where ProductID = '{0}'", pid, typeid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 获取分类下所有商品详细数据
/// </summary>
/// <param name="html"></param>
public void ParseWareTypeData(string html, string myType)
{
try
{
//string re = "<(link|script)(.*?type)[^>]+?(/)?>(?(3)|\\s*</\\1>)"; 提取页面link和Script
//string regStr = @"var pay_after = \[(\d{1,16}\,){58}(\d{1,16})\];";
//var slaveWareList ={(.*)};
//var pay_after = \[(.*)\];
string regStr = @"var pay_after =\s*\[(\d{1,16}\,)+(\d{1,16})\];";
string rtnData = Regex.Match(html, regStr).Value;
ShowGetMessage("解析页面商品...");
if (!string.IsNullOrEmpty(rtnData))
{
string tt = rtnData.Replace("var pay_after = [", "").Replace("];", "");
string[] wareList = tt.Split(',');
if (wareList != null && wareList.Length > 0)
{
ShowGetMessage(string.Format("当前页面共计{0}个商品", wareList.Length));
foreach (var item in wareList)
{
iStep++;
if (!DBHelper.GetInstance().WareIsExists(item))
{
ShowGetMessage(string.Format("获取商品[{0}]信息", item));
ProductInfo tmpWare = WareService.GetInstance().GetWareInfoByID(item);
if (tmpWare != null)
{
tmpWare.ProductType = myType;
ShowGetMessage(string.Format("商品[{0}]{1}信息入库...", item, tmpWare.ProductName));
DBHelper.GetInstance().WareInsert(tmpWare);
DBHelper.GetInstance().WarePriceInsert(tmpWare.ProductID, tmpWare.ProductPrice);
ShowGetMessage(string.Format("商品[{0}]{1}信息入库完成。", item, tmpWare.ProductName));
}
}
ShowGetStep(iStep);
}
}
}
}
catch (Exception ex)
{
ShowGetMessage(string.Format("商品处理异常：{0}", ex.Message));
OtCom.XLogErr(ex.Message);
}
}
/// <summary>
/// 更新商品底价
/// </summary>
/// <param name="pid">商品编号</param>
public void UpdateHistoryPriceBasebyID(string pid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set ProductBasePrice=(select min(price) from ProductPriceHistory t "
+ "where t.pid = '{0}') where ProductInfo.ProductID = '{1}'", pid, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
public void WareRepositoryUpdate(string pid, string dispatch)
{
OtDB db = GetDb();
try
{
db.Begin();
//价格对比 --> 涨价、持平、走低、未知
string sql = string.Format("update ProductInfo set ProductDispatchMode = '{0}' where ProductID = '{1}';", dispatch, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新评价统计信息
/// </summary>
/// <param name="pid">商品编号</param>
/// <param name="count">评价总数</param>
/// <param name="good">好评</param>
/// <param name="general">中评</param>
/// <param name="poor">差评</param>
/// <param name="tag">印象</param>
public void WareMessageUpdate(string pid, int count, double good, double general, double poor, string tag)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update ProductInfo set ProductEvaluateCount = {0}, ProductGoodRate = {1}, ProductPoorRate = {2}," +
"ProductGeneralRate = {3}, ProductHotCommentTag = '{4}' where ProductID = '{5}';", count, good, general, poor, tag, pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 插入商品信息
/// </summary>
/// <param name="_myProduct"></param>
public void WareInsert(ProductInfo _myProduct)
{
OtDB db = GetDb();
try
{
db.Begin();
db.ExecInsert(_myProduct, "ProductInfo", new string[] { "RID", "ProductID", "ProductName", "ProductPrice", "ProductIsSaled",
"ProductEvaluateCount", "ProductGoodRate", "ProductImagePath", "ProductURL", "ProductDetail", "ProductBrand",
"ProductAttach", "NativeData", "CreateTime", "CreateUser", "BEnable" });
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
