/// <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();
}
}
/// <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>
/// <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 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>
/// <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="_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="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>
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 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="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();
}
}
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="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="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();
}
}
/// <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="_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();
}
}
/// <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();
}
}
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>
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();
}
}
/// <summary>
/// 删除单个商品
/// </summary>
/// <param name="rid"></param>
public void WareRemoveOne(string pid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("delete from ProductInfo where productid ='{0}'", pid);
db.Exec(sql);
sql = string.Format("delete from ProductPriceHistory where pid='{0}'", pid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 获取评价信息
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public List <ProductMessage> WareMessageGet(string pid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select * from ProductMessage where PID = '{0}'", pid);
List <ProductMessage> msgs = db.Query <ProductMessage>(sql);
db.Commit();
return(msgs);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
return(null);
}
}
/// <summary>
/// 获取京东商品分类数据
/// </summary>
/// <param name="wherestr"></param>
/// <returns></returns>
public List <JDWareType> GetWareJDTypes(string wherestr)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select * from JDWareType where BEnable = 1 {0}", wherestr);
List <JDWareType> rtns = db.Query <JDWareType>(sql);
db.Commit();
return(rtns);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
return(null);
}
}
public void WarePriceUpdate(string pid, double price, WarePriceType priceSrc)
{
OtDB db = GetDb();
try
{
string sql = "";
switch (priceSrc)
{
case WarePriceType.WebPrice:
sql = string.Format("update ProductInfo set ProductPrice = {0},ProductPriceDate = DateTime('now') where ProductID = '{1}';", price, pid);
break;
case WarePriceType.AppPrice:
sql = string.Format("update ProductInfo set ProductMobilePrice = {0},ProductPriceDate = DateTime('now') where ProductID = '{1}';", price, pid);
break;
case WarePriceType.QQPrice:
sql = string.Format("update ProductInfo set ProductQQPrice = {0},ProductPriceDate = DateTime('now') where ProductID = '{1}';", price, pid);
break;
case WarePriceType.WxPrice:
sql = string.Format("update ProductInfo set ProductWXPrice = {0},ProductPriceDate = DateTime('now') where ProductID = '{1}';", price, pid);
break;
default:
break;
}
if (string.IsNullOrEmpty(sql))
{
return;
}
db.Begin();
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 更新系统参数值
/// </summary>
/// <param name="pKey"></param>
/// <param name="pValue"></param>
public void UpdateSysParam(string pKey, string pValue)
{
//首先判断参数是否存在
InsertSysParam(pKey, pValue);
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("update SysParams set ParamValue = '{0}' where ParamKey = '{1}';", pValue, pKey);
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[] { "ProductName", "BrandID", "ProductBrand", "ProductAttach", "ProductIsSaled",
"ProductTag", "Catalog", "ProductImageWebPath",
"ProductImagePath", "ProductURL", "ProductCoupon", "ProductPromoMsg", "ProductDispatchMode",
"ProductPrice", "ProductType", "ProductMobilePrice", "ProductQQPrice", "ProductWXPrice", //"ProductPriceTrend", "ProductBasePrice",
"ProductGeneralRate", "ProductPoorRate", "ProductHotCommentTag", "ProductEvaluateCount", "ProductGoodRate", "Focus" },
string.Format(" ProductID = '{0}'", _myProduct.ProductID), null);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 删除商品类型
/// </summary>
/// <param name="tid"></param>
public void WareTypeDelete(string tid)
{
OtDB db = GetDb();
try
{
db.Begin();
//将商品中分类数据置空
string sql = string.Format("update Productinfo set ProductType='' where ProductType = '{0}'", tid);
db.Exec(sql);
//删除商品分类
sql = string.Format("delete from ProductType where tid = '{0}'", tid);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 获取某个商品当前日期前的最后价格
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public double WareLastPriceGet(string pid)
{
double rtnPrice = 0;
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select price,max(PriceDate) pricetime from ProductPriceHistory where pid = {0} and Date(PriceDate) < Date(DateTime('now'));", pid);
//string sql = string.Format("select ProductPrice from ProductInfo where ProductID = '{0}';", pid);
rtnPrice = db.QueryOneCol <double>(sql);
db.Commit();
return(rtnPrice);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
return(0);
}
}
/// <summary>
/// 添加评价信息
/// </summary>
/// <param name="msg"></param>
public void WareMessageAdd(ProductMessage msg)
{
if (msg == null)
{
return;
}
OtDB db = GetDb();
try
{
db.Begin();
//db.Exec(string.Format("delete from ProductMessage where PID = '{0}'", msg.PID));
db.ExecInsert(msg, "ProductMessage", new string[] { "PID", "MsgType", "MsgContent", "MsgUser", "MsgUserLevel", "MsgProvince", "MsgDate" });
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", "SkuID", "SkuidKey", "VenderId", "ShopId", "ProductName",
"BrandID", "ProductBrand", "ProductAttach", "ProductIsSaled", "ProductTag", "Catalog", "ProductImageWebPath",
"ProductImagePath", "ProductURL", "ProductCoupon", "ProductPromoMsg", "ProductDispatchMode",
"ProductPrice", "ProductBasePrice", "ProductMobilePrice", "ProductQQPrice", "ProductWXPrice", "ProductType", "ProductPriceTrend",
"ProductGeneralRate", "ProductPoorRate", "ProductHotCommentTag", "ProductEvaluateCount", "ProductGoodRate",
"CreateTime", "CreateUser", "Focus", "BEnable" });
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}