/// <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);
}
}
/// <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);
}
}
/// <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);
}
}
/// <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="pid">上级编号</param>
/// <param name="level">等级</param>
public List <DispatchArea> GetWareArea(int pid, int level)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select * from DispatchArea where parentid = {0} and level = {1}", pid, level);
List <DispatchArea> myAreas = db.Query <DispatchArea>(sql, null);
db.Commit();
return(myAreas);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}
public List <TabSysParams> GetSysParams()
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select * from SysParams");
List <TabSysParams> myParams = db.Query <TabSysParams>(sql, null);
db.Commit();
//ParseSysParams(myParams);
return(myParams);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
return(null);
//db.Rollback();
}
}
/// <summary>
/// 获取商品历史价格 只取部分，重复的价格将抛弃部分点
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
public List <ProductPriceHistory> WarePriceHistoryGetMore(string pid)
{
OtDB db = GetDb();
try
{
db.Begin();
string sql = string.Format("select rowid as RID,*,datetime(PriceDate) as PriceTime from ProductPriceHistory where pid = '{0}' "
+ "order by PriceDate desc", pid);
List <ProductPriceHistory> rtnList = db.Query <ProductPriceHistory>(sql, null);
db.Commit();
return(rtnList);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}
/// <summary>
/// 获取某个类别商品数据
/// </summary>
/// <param name="typeid"></param>
/// <param name="wName"></param>
/// <param name="wStock"></param>
/// <param name="wPriceTrend"></param>
/// <param name="wAttach"></param>
/// <returns></returns>
public List <ProductInfo> WareGetAll(string typeid, string wName, string wStock, string wPriceTrend, string wAttach)
{
OtDB db = GetDb();
try
{
db.Begin();
string wStr = string.IsNullOrEmpty(wName) ? "" : string.Format("where ProductName like '%{0}%'", wName);
string sql = string.Format("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 {0} where a.BEnable=1 order by a.CreateTime", wStr);
List <ProductInfo> rtnList = db.Query <ProductInfo>(sql, null);
db.Commit();
return(rtnList);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}
public List <ProductType> WareTypeGet()
{
OtDB db = GetDb();
try
{
db.Begin();
//分页查询
//select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
//select * from account limit10,9
string sql = "select TID,Name from ProductType order by CreateTime";
List <ProductType> rtnList = db.Query <ProductType>(sql, null);
db.Commit();
return(rtnList);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}
/// <summary>
/// 获取所有商品信息
/// </summary>
/// <returns></returns>
public List <ProductInfo> WareGetAll()
{
OtDB db = GetDb();
try
{
db.Begin();
//分页查询
//select * from users order by id limit 10 offset 0;//offset代表从第几条记录“之后“开始查询，limit表明查询多少条结果
//select * from account limit10,9
string sql = "select a.*,(case a.ProductIsSaled when 1 then '有货' when 2 then '配货' else '无货' end) as ProductStock, b.Name as TypeName from ProductInfo a left join Dic_ProductType b on a.ProductType = b.TID order by a.CreateTime";
List <ProductInfo> rtnList = db.Query <ProductInfo>(sql, null);
db.Commit();
return(rtnList);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}
/// <summary>
/// 获取商品历史价格
/// </summary>
/// <param name="pid">商品编号</param>
/// <returns></returns>
public List <ProductPriceHistory> WarePriceHistoryGet(string pid)
{
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}' order by PriceDate desc", pid);
List <ProductPriceHistory> rtnList = db.Query <ProductPriceHistory>(sql, null);
db.Commit();
return(rtnList);
}
catch (Exception ex)
{
OtCom.XLogErr(ex.Message);
//db.Rollback();
return(null);
}
}