/// <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="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="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>
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="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="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="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="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>
/// <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>
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();
}
}
public void WarePriceInsert(ProductPriceHistory price)
{
OtDB db = GetDb();
try
{
db.Begin();
//sqlite用的全球时间UTC,要用datetime()函数转换若干 localtime UTC
//用data_time.ToString("s");这种方法转换成 iso 8601标准字符串格式
string sql = string.Format("insert into ProductPriceHistory (PID,Price,PriceDate,PriceType) select '{0}',{1},'{2}','{3}'", price.PID, price.Price, price.PriceDate.ToString("s"), price.PriceType);
db.Exec(sql);
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
}
}
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="pKey"></param>
/// <param name="PValue"></param>
private void InsertSysParam(string pKey, string PValue)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select * from SysParams where ParamKey = '{0}';", pKey);
TabSysParams rtn = db.QueryOneRow <TabSysParams>(sql);
if (rtn == null)
{
sql = string.Format("insert into SysParams (ParamKey,ParamValue,ParamDescription) values ('{0}','{1}','');", pKey, PValue);
db.Exec(sql);
}
db.Commit();
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
db.Rollback();
}
}
/// <summary>
/// 插入商品价格
/// </summary>
/// <param name="pid">商品编号</param>
/// <param name="price">商品价格</param>
/// <param name="dt">入库时间</param>
/// <param name="origin">数据来源 如京东、手机、超市</param>
public void WarePriceInsert(string pid, double price, DateTime dt, string origin)
{
OtDB db = GetDb();
try
{
//先查找当天有没有相同的数据
if (!WarePriceHistoryGetOne(pid, dt, price))
{
db.Begin();
//string sql = string.Format("select * from ProductPriceHistory");
//sqlite用的全球时间UTC,要用datetime()函数转换若干 localtime UTC
//用data_time.ToString("s");这种方法转换成 iso 8601标准字符串格式
string sql = string.Format("insert into ProductPriceHistory (PID, Price, PriceDate, PriceType) select '{0}',{1},'{2}','{3}'", pid, price, dt.ToString("s"), origin);
db.Exec(sql);
db.Commit();
}
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
}
}