Example #1
0
        /// <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));
        }
Example #2
0
        /// <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));
        }
Example #3
0
        /// <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));
        }
Example #4
0
        /// <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));
        }
Example #5
0
        /// <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]));
        }
Example #6
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);
        }
Example #7
0
        /// <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));
        }
Example #8
0
        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);
        }