/// <summary>
/// 读取待盘点报表
/// </summary>
/// <param name="option"></param>
/// <returns></returns>
public DataSet ReadStoreDetailInventory(ReportOption option)
{
string sql = string.Format(@"select Id as '库存ID',StoreId as '仓库',StoreZone as '货架编号',GoodsId as '商品编号',TypeName as '商品类别',DisplayName as '商品名称',Num as '当前库存量',Unit as '单位',0 as'盘点数量' from View_GoodsStore where BranchId={0} and StoreZone<>'收货区'", option.BranchId);
if (option.StoreId > 0)
{
sql += string.Format(" and StoreId={0} ", option.StoreId);
}
if (option.TypeId > 0)
{
GoodsType gt = new GoodsType();
gt.Id = option.TypeId;
gt.Load();
sql += string.Format(" and Code like '{0}%' ", gt.Code);
}
if (option.KeyWords != "")
{
sql += string.Format(" and DisplayName like '%{0}%' ", option.KeyWords);
}
if (option.SalesId != 0)//重用了该字段：只显示库存不为零的商品
{
sql += " and Num <> 0 ";
}
sql += " order by StoreZone desc ";
return(m_dbo.GetDataSet(sql));
}
/// <summary>
/// 读取 销售排行
/// </summary>
/// <returns></returns>
public DataSet ReadSalesRank(int topNum)
{
string sql = "";
if (SnapOption.IsUseSiteGoods > 0)
{
sql = string.Format(@"select top {0} * from View_GoodsBranch where 1=1 and IsVisible=1 and IsShelves=1 and (ParentId =0 or ParentId=2)", topNum);
if (SnapOption.BranchId > 1)
{
sql += string.Format(" and BranchId={0}", SnapOption.BranchId);
}
}
else
{
sql = string.Format(@"select top {0} * from View_Goods where 1=1 and IsVisible=1 and IsShelves=1 and (ParentId =0 or ParentId=2)", topNum);
}
if (SnapOption.TypeId != 0)
{
GoodsType gt = new GoodsType();
gt.Id = SnapOption.TypeId;
gt.Load();
sql += string.Format(" and Code like '{0}%' ", gt.Code);
}
if (SnapOption.Order == 0)
{
sql += " order by SaleNumber desc,SaleCount desc ";
}
else
{
sql += " order by SaleCount desc,SaleNumber desc ";
}
return(m_dbo.GetDataSet(sql));
}
/// <summary>
/// 首页商品的读取 暂时不用 q 2015-7-16
/// </summary>
/// <returns></returns>
public DataSet ReadGoodsWebSiteShow()
{
string sql = string.Format(@" select * from (select *,(select COUNT(*) from Goods gs where gs. ParentId=g.ID) as c from dbo.Goods g where ParentId=0) g where g.c=0 UNION
select * from (select *,ROW_NUMBER() over(partition by parentId order by recommend) as new_index from dbo.Goods where ParentId !=0 ) GoodsNew where GoodsNew.new_index=1 ");
if (SnapOption.TypeId != 0)
{
GoodsType gt = new GoodsType();
gt.Id = SnapOption.TypeId;
gt.Load();
sql += string.Format(" and Code like '{0}%' ", gt.Code);
}
return(m_dbo.GetDataSet(sql));
}
/// <summary>
/// 通过商品的类别查找出,相应的畅销商品 2014-1-20 add by quxiaoshan
/// </summary>
/// <returns></returns>
public DataSet ReadHotByGoodsType()
{
string sqlWhere = " and Recommend=6 and dbo.Goods.IsVisible=1 ";
string sql = string.Format("select *,dbo.Goods.ID as GoodsId from dbo.GoodsType join dbo.Goods on dbo.GoodsType.ID=dbo.Goods.TypeId where 1=1 {0}", sqlWhere);
if (m_Id != 0)
{
GoodsType gt = new GoodsType();
gt.Id = m_Id;
gt.Load();
sql += string.Format(" and Code like '{0}%' ", gt.Code);
}
sql += " order by dbo.Goods.Recommend desc ";
return(m_dbo.GetDataSet(sql, (pageModel.CurrentPage - 1) * pageModel.PageSize, pageModel.PageSize));
}
/// <summary>
/// 畅销品的商品总条数
/// </summary>
/// <returns></returns>
public int ReadHotTotalRows()
{
string sqlWhere = " and Recommend=6 and dbo.Goods.IsVisible=1 ";
string sql = string.Format("select COUNT(*) from dbo.GoodsType join dbo.Goods on dbo.GoodsType.ID=dbo.Goods.TypeId where 1=1 {0}", sqlWhere);
if (m_Id != 0)
{
GoodsType gt = new GoodsType();
gt.Id = m_Id;
gt.Load();
sql += string.Format(" and Code like '{0}%' ", gt.Code);
}
DataSet ds = m_dbo.GetDataSet(sql);
return(Convert.ToInt32(ds.Tables[0].Rows[0][0]));
}
/// <summary>
/// 商品积分的计算
/// </summary>
/// <returns></returns>
public double GetGoodsPoint(Goods goods)
{
double point = 0;
if (goods.Rate > 0) //商品的积分计算方法
{
point = goods.Price * goods.Rate / 100;
}
else
{
GoodsType goodstype = new GoodsType();
if (goodstype.Load(goods.TypeId))
{
double rate = goodstype.Rate;
point = goods.Price * rate / 100;
}
}
return(point);
}
/// <summary>
/// 读取库存明细
/// </summary>
/// <param name="option"></param>
/// <returns></returns>
public DataSet ReadStoreDetail(ReportOption option)
{
string sql = string.Format(@"select BranchId,StoreId,TypeId,TypeName,Code,GoodsId,DisplayName,Num,Unit,AC,StoreZone
from View_GoodsStore where BranchId={0} and StoreZone<>'收货区'", option.BranchId);
if (option.StoreId > 0)
{
sql += string.Format(" and StoreId={0} ", option.StoreId);
}
if (option.TypeId > 0)
{
GoodsType gt = new GoodsType();
gt.Id = option.TypeId;
gt.Load();
sql += string.Format(" and Code like '{0}%' ", gt.Code);
}
if (option.KeyWords != "")
{
sql += string.Format(" and DisplayName like '%{0}%' ", option.KeyWords);
}
if (option.SalesId != 0)//重用了该字段：只显示库存不为零的商品
{
sql += " and Num <> 0 ";
}
if (option.GoodsStoreID != 0)
{
sql += string.Format(" and Id={0} ", option.GoodsStoreID);
}
if (option.GoodsID != 0)
{
sql += string.Format(" and GoodsId={0} ", option.GoodsID);
}
sql += "order by Code ";
return(m_dbo.GetDataSet(sql));
}
private string GetSQLWhere()
{
string sql = "";
//判断是否只显示可见商品
if (SnapOption.IsVisible == 1)
{
sql += " and IsVisible=1 ";
}
else if (SnapOption.IsVisible == 0)
{
sql += " and IsVisible=0 ";
}
else if (SnapOption.IsVisible == 2)//读出非禁用商品
{
sql += " and IsVisible >=0 ";
}
if (SnapOption.IsHideParentGoods == 1)
{
//读取基本商品 和子商品
sql += " and( parentId =0 or parentId >2) ";
}
else
{
//只读取基本商品和组合商品
sql += " and ParentId <=2 ";
}
if (SnapOption.IsAllow > -1)
{
sql += string.Format(" and IsAllow={0} ", SnapOption.IsAllow);
}
if (SnapOption.IsPublic > -1)
{
sql += string.Format(" and IsPublic ={0} ", SnapOption.IsPublic);
}
if (SnapOption.IsShelves != 1)
{
//只展示上架的商品，未修改以前展示上架下架的商品 update by hjy 2016-10-12
sql += string.Format(" and IsShelves ={0} ", 1);
}
if (SnapOption.IsUseSiteGoods > 0)//开启切换站点更改商品
{
if (SnapOption.BranchId > 0)
{
sql += string.Format(" and BranchId={0}", SnapOption.BranchId);
}
}
else
{
if (SnapOption.BranchId == 1)
{
sql += " and BranchId=1 ";
}
else if (SnapOption.BranchId > 1)
{
sql += string.Format(" and ( BranchId={0} or BranchId=1)", SnapOption.BranchId);
}
}
if (SnapOption.TypeId != 0)
{
GoodsType gt = new GoodsType();
gt.Id = SnapOption.TypeId;
gt.Load();
sql += string.Format(" and Code like '{0}%' ", gt.Code);
}
if (SnapOption.PCode != "" && SnapOption.PCode != null)
{
sql += string.Format(" and Code like '{0}%' ", SnapOption.PCode);
}
if (SnapOption.BrandId > 0)
{
sql += string.Format(" and BrandId = {0} ", SnapOption.BrandId);
}
if (SnapOption.StartPrice >= 0)
{
sql += string.Format(" and Price >={0} ", SnapOption.StartPrice);
}
if (SnapOption.EndPrice > 0)
{
sql += string.Format(" and Price < {0} ", SnapOption.EndPrice);
}
if (SnapOption.StartMaolilv > -1)
{
sql += string.Format(" and Maolilv >= {0} ", SnapOption.StartMaolilv);
}
if (SnapOption.EndMaolilv > -1)
{
sql += string.Format(" and Maolilv <= {0} ", SnapOption.EndMaolilv);
}
if (SnapOption.NoPhoto == 1)
{
sql += " and (HomeImage = '' or PhotoNum = 0 ) ";
}
if (SnapOption.ShortageBarCode == 1)
{
sql += " and (BarCodeNum = 0 or BarCodeNum < AllBarCodeNum) ";
}
else
{
if (SnapOption.NoBarCode == 1)
{
sql += " and BarCodeNum = 0 ";
}
}
if (SnapOption.Cuxiao == 1)
{
sql += " and Cuxiao>0 ";
}
if (SnapOption.Hot == 1)
{
sql += " and IsHot =1 ";
}
if (SnapOption.New == 1)
{
sql += " and IsNew = 1 ";
}
if (SnapOption.Memo != "")
{
sql += string.Format(" and Memo='{0}'", SnapOption.Memo);
}
if (SnapOption.KeyWords != "" && SnapOption.KeyWords != null && SnapOption.KeyWords != "NULL")
{
if (SnapOption.KeyWords.Length > 2)
{
SnapOption.KeyWords = CommenClass.StringTools.SplitKeyWords(SnapOption.KeyWords);
}
char[] sep = { ' ' };
string[] kws = SnapOption.KeyWords.Split(sep, StringSplitOptions.RemoveEmptyEntries);
if (kws.Length == 1)
{
if (kws[0].Length <= 2)//关键字太短的时候，只从 SN和GoodsName中筛选
{
sql += string.Format(" and ((PY like '%{0}%') or (DisplayName like '%{0}%')) ", kws[0]);
}
else
{
sql += string.Format(" and ((GoodsId like '%{0}%') or (DisplayName like '%{0}%') or (PY like '%{0}%') or (Feature like '%{0}%')) ", kws[0]);
//sql += string.Format(" and ((DisplayName like '%{0}%') or (PY like '%{0}%') or (Feature like '%{0}%')) ", kws[0]);
}
}
else
{
for (int i = 0; i < kws.Length; i++)
{
sql += string.Format(" and ((DisplayName like '%{0}%') or (PY like '%{0}%') or (Feature like '%{0}%')) ", kws[i]);
}
}
}
return(sql);
}