public StoreProductModel GetStoreProductInfoForStoreApp(int StoreID, int productId) { StoreProductModel storeProductModel = null; DbCommand sqlStringCommand = base.database.GetSqlStringCommand("SELECT ProductId,ProductName,ProductCode,ThumbnailUrl410 FROM Hishop_Products WHERE ProductId = @ProductId;SELECT skus.ProductId,\r\n\t skus.SkuId,\t\r\n\t skus.SKU,\r\n\t skus.SalePrice, \r\n skus.CostPrice,\r\n skus.[Weight],\r\n skus.Stock,\r\n ssku.Stock as StoreStock,\r\n ssku.WarningStock,\r\n case when StoreSalePrice=0 then SalePrice else StoreSalePrice end storeSalePrice\r\n FROM Hishop_StoreSKUs AS ssku\r\n\t inner JOIN Hishop_SKUs AS skus ON skus.SkuId = ssku.SkuId \t \r\n\t WHERE ssku.ProductId = @ProductId AND ssku.StoreId = @StoreID"); base.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId); base.database.AddInParameter(sqlStringCommand, "StoreID", DbType.Int32, StoreID); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { storeProductModel = DataHelper.ReaderToModel <StoreProductModel>(dataReader); if (storeProductModel != null) { dataReader.NextResult(); while (dataReader.Read()) { string key = (string)((IDataRecord)dataReader)["SkuId"]; if (!storeProductModel.Skus.ContainsKey(key)) { SKUItem sKUItem = DataMapper.PopulateSKU(dataReader); sKUItem.StoreSalePrice = ((IDataRecord)dataReader)["StoreSalePrice"].ToDecimal(0); storeProductModel.Skus.Add(key, sKUItem); } } } } return(storeProductModel); }
public StoreProductModel GetStoreProductInfo(int StoreID, int productId) { StoreProductModel storeProductModel = null; DbCommand sqlStringCommand = base.database.GetSqlStringCommand("SELECT * FROM Hishop_Products WHERE ProductId = @ProductId;SELECT skus.ProductId, skus.SkuId, s.AttributeId, s.ValueId, skus.SKU, skus.SalePrice, skus.CostPrice, (select Stock from Hishop_StoreSKUs where SkuId = skus.SkuId And StoreId = @StoreId) as Stock,StoreStock=stock,(select WarningStock from Hishop_StoreSKUs where SkuId = skus.SkuId And StoreID = @StoreID) as WarningStock, skus.[Weight] FROM Hishop_SKUItems s right outer join Hishop_SKUs skus on s.SkuId = skus.SkuId WHERE skus.ProductId = @ProductId ORDER BY (SELECT DisplaySequence FROM Hishop_Attributes WHERE AttributeId = s.AttributeId) DESC;"); base.database.AddInParameter(sqlStringCommand, "ProductId", DbType.Int32, productId); base.database.AddInParameter(sqlStringCommand, "StoreID", DbType.Int32, StoreID); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { storeProductModel = DataHelper.ReaderToModel <StoreProductModel>(dataReader); if (storeProductModel != null) { dataReader.NextResult(); while (dataReader.Read()) { string key = (string)((IDataRecord)dataReader)["SkuId"]; if (!storeProductModel.Skus.ContainsKey(key)) { storeProductModel.Skus.Add(key, DataMapper.PopulateSKU(dataReader)); } if (((IDataRecord)dataReader)["AttributeId"] != DBNull.Value && ((IDataRecord)dataReader)["ValueId"] != DBNull.Value) { storeProductModel.Skus[key].SkuItems.Add((int)((IDataRecord)dataReader)["AttributeId"], (int)((IDataRecord)dataReader)["ValueId"]); } } } } return(storeProductModel); }
public IList <StoreProductModel> GetNoStockStoreProductList(int storeId) { IList <StoreProductModel> list = new List <StoreProductModel>(); DbCommand sqlStringCommand = base.database.GetSqlStringCommand("SELECT distinct(ProductID) from Hishop_StoreSKUs where StoreId = @StoreId AND (Stock <= WarningStock OR Stock <= 0)"); base.database.AddInParameter(sqlStringCommand, "StoreID", DbType.Int32, storeId); using (IDataReader dataReader = base.database.ExecuteReader(sqlStringCommand)) { while (dataReader.Read()) { StoreProductModel storeProductInfo = this.GetStoreProductInfo(storeId, ((IDataRecord)dataReader)["ProductId"].ToInt(0)); if (storeProductInfo != null) { list.Add(storeProductInfo); } } } return(list); }