public DbQueryResult GetStoreViewPrducts(StoreStockQuery query)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append($" ProductID in (select distinct(ProductID) FROM Hishop_StoreSKUs WHERE StoreID = {query.StoreID})");
stringBuilder.AppendFormat(" AND SaleStatus = {0}", 1);
string str = " ProductId, ProductCode,ProductName,ProductType,ThumbnailUrl40,ThumbnailUrl220, MarketPrice, ";
str += string.Format("case when (SELECT MIN(StoreSalePrice) FROM dbo.Hishop_StoreSKUs WHERE ProductId = p.ProductId and StoreId='{0}')=0 then (SELECT MIN(SalePrice) FROM dbo.Hishop_SKUs WHERE ProductId = p.ProductId ) else (SELECT MIN(StoreSalePrice) FROM dbo.Hishop_StoreSKUs WHERE ProductId = p.ProductId and StoreId='{0}') end AS SalePrice", query.StoreID);
return(DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_BrowseProductList p", "ProductId", stringBuilder.ToString(), str));
}
private void GetStoreProducts(HttpContext context)
{
if (!string.IsNullOrEmpty(context.Request["PageSize"]) && !string.IsNullOrEmpty(context.Request["CurrentPage"]))
{
int pageSize = int.Parse(context.Request["PageSize"]);
int pageIndex = int.Parse(context.Request["CurrentPage"]);
string text = context.Request["sortBy"];
string text2 = context.Request["sortAction"];
int storeID = 0;
int.TryParse(context.Request["storeId"], out storeID);
StoreStockQuery storeStockQuery = new StoreStockQuery();
storeStockQuery.StoreID = storeID;
storeStockQuery.PageIndex = pageIndex;
storeStockQuery.PageSize = pageSize;
if (string.IsNullOrEmpty(text))
{
storeStockQuery.SortBy = "ProductId";
}
else
{
storeStockQuery.SortBy = text;
}
if (!string.IsNullOrEmpty(text2))
{
storeStockQuery.SortOrder = ((text2 == "1") ? SortAction.Asc : SortAction.Desc);
}
DbQueryResult storeViewPrducts = StoresHelper.GetStoreViewPrducts(storeStockQuery);
DataTable data = storeViewPrducts.Data;
string str = "{\"totalCount\":\"" + storeViewPrducts.TotalRecords + "\",\"data\":[";
string text3 = "";
SiteSettings masterSettings = SettingsManager.GetMasterSettings();
for (int i = 0; i < data.Rows.Count; i++)
{
if (text3 != "")
{
text3 += ",";
}
text3 = text3 + "{\"ProductId\":\"" + data.Rows[i]["ProductId"] + "\",\"ProductName\":\"" + data.Rows[i]["ProductName"] + "\",\"ProductType\":\"" + data.Rows[i]["ProductType"] + "\",\"SalePrice\":\"" + ((decimal)data.Rows[i]["SalePrice"]).F2ToString("f2") + "\",\"ThumbnailUrl220\":\"" + (string.IsNullOrEmpty(data.Rows[i]["ThumbnailUrl220"].ToString()) ? masterSettings.DefaultProductImage : data.Rows[i]["ThumbnailUrl220"].ToString()) + "\"}";
}
str += text3;
str += "]}";
context.Response.Write(str);
}
}
public DbQueryResult GetStorePrducts(StoreStockQuery query)
{
StringBuilder stringBuilder = new StringBuilder();
if (query.IsRelation)
{
stringBuilder.Append($" ProductID in(select distinct(ProductID) FROM Hishop_StoreSKUs where StoreID = {query.StoreID}");
}
else
{
stringBuilder.Append($" ProductID not in(select distinct(ProductID) FROM Hishop_StoreSKUs where StoreID = {query.StoreID}");
}
if (query.SaleStatus != ProductSaleStatus.All)
{
stringBuilder.AppendFormat(" AND SaleStatus = {0}", (int)query.SaleStatus);
}
else
{
stringBuilder.AppendFormat(" AND SaleStatus <> ({0})", 0);
}
if (query.BrandId.HasValue)
{
stringBuilder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
}
if (query.TypeId.HasValue)
{
stringBuilder.AppendFormat(" AND TypeId = {0}", query.TypeId.Value);
}
if (query.TagId.HasValue)
{
stringBuilder.AppendFormat(" AND ProductId IN (SELECT ProductId FROM Hishop_ProductTag WHERE TagId={0})", query.TagId);
}
if (!string.IsNullOrEmpty(query.Keywords))
{
query.Keywords = DataHelper.CleanSearchString(query.Keywords);
string[] array = Regex.Split(query.Keywords.Trim(), "\\s+");
stringBuilder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(array[0]));
for (int i = 1; i < array.Length && i <= 4; i++)
{
stringBuilder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(array[i]));
}
}
if (!string.IsNullOrEmpty(query.ProductCode))
{
stringBuilder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
}
if (query.CategoryId.HasValue)
{
if (query.CategoryId.Value > 0)
{
stringBuilder.AppendFormat(" AND (MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%' OR ExtendCategoryPath1 LIKE '{0}|%' OR ExtendCategoryPath2 LIKE '{0}|%' OR ExtendCategoryPath3 LIKE '{0}|%' OR ExtendCategoryPath4 LIKE '{0}|%') ", query.MaiCategoryPath);
}
else
{
stringBuilder.Append(" AND (CategoryId = 0 OR CategoryId IS NULL)");
}
}
if (query.IsWarningStock)
{
stringBuilder.Append(" AND WarningStockNum>0");
}
string selectFields = string.Format("CategoryId,ProductId, ProductCode,IsMakeTaobao,ProductName, ThumbnailUrl40, MarketPrice, SalePrice,ExtendCategoryPath,ExtendCategoryPath1,ExtendCategoryPath2,ExtendCategoryPath3,ExtendCategoryPath4, (SELECT CostPrice FROM Hishop_SKUs WHERE SkuId = p.SkuId) AS CostPrice, (SELECT SUM(Stock) FROM Hishop_StoreSKUs WHERE ProductId = p.ProductId And StoreID={0}) AS Stock,(SELECT TOP 1 [WarningStock] FROM Hishop_StoreSKUs WHERE ProductId = p.ProductId And StoreID={0}) AS WarningStock, DisplaySequence,SaleStatus", query.StoreID);
return(DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_BrowseProductList p", "ProductId", stringBuilder.ToString(), selectFields));
}
