private AdvancedProductQuery GetQuery()
{
AdvancedProductQuery entity = new AdvancedProductQuery();
entity.Keywords = _productName;
entity.ProductCode = _productCode;
entity.CategoryId = _categoryId;
entity.ProductLineId = _lineId;
entity.PageSize = pager.PageSize;
entity.PageIndex = pager.PageIndex;
entity.SaleStatus = ProductSaleStatus.OnSale;
entity.SortOrder = SortAction.Desc;
entity.SortBy = "DisplaySequence";
entity.StartDate = _startDate;
entity.EndDate = _endDate;
entity.IncludeInStock = _includeInStock;
entity.IncludeOnSales = _includeOnSales;
entity.IncludeUnSales = _includeUnSales;
if (this._categoryId.HasValue)
{
entity.MaiCategoryPath = CatalogHelper.GetCategory(this._categoryId.Value).Path;
}
Globals.EntityCoding(entity, true);
return entity;
}
public DbQueryResult GetExportProducts(AdvancedProductQuery query, string removeProductIds)
{
int? nullable;
StringBuilder builder = new StringBuilder();
builder.Append("(");
if (query.IncludeOnSales)
{
builder.AppendFormat("SaleStatus = {0} OR ", 1);
}
if (query.IncludeUnSales)
{
builder.AppendFormat("SaleStatus = {0} OR ", 2);
}
if (query.IncludeInStock)
{
builder.AppendFormat("SaleStatus = {0} OR ", 3);
}
builder.Remove(builder.Length - 4, 4);
builder.Append(")");
if (query.BrandId.HasValue)
{
builder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
}
if (query.IsMakeTaobao.HasValue && (((nullable = query.IsMakeTaobao).GetValueOrDefault() != -1) || !nullable.HasValue))
{
builder.AppendFormat(" AND IsMakeTaobao={0} ", query.IsMakeTaobao);
}
if (!string.IsNullOrEmpty(query.Keywords))
{
query.Keywords = DataHelper.CleanSearchString(query.Keywords);
string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[0]));
for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
{
builder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[i]));
}
}
if (!string.IsNullOrEmpty(query.ProductCode))
{
builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
}
if (query.CategoryId.HasValue && (query.CategoryId.Value > 0))
{
builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%' )", query.MaiCategoryPath);
}
if (query.StartDate.HasValue)
{
builder.AppendFormat(" AND AddedDate >='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
builder.AppendFormat(" AND AddedDate <='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (!string.IsNullOrEmpty(removeProductIds))
{
builder.AppendFormat(" AND ProductId NOT IN ({0})", removeProductIds);
}
string selectFields = "ProductId, ProductCode, ProductName, ThumbnailUrl40, MarketPrice, SalePrice, (SELECT CostPrice FROM Hishop_SKUs WHERE SkuId = p.SkuId) AS CostPrice, Stock, DisplaySequence";
return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_BrowseProductList p", "ProductId", builder.ToString(), selectFields);
}
public DataSet GetExportProducts(AdvancedProductQuery query, bool includeCostPrice, bool includeStock, string removeProductIds)
{
int? nullable;
StringBuilder builder = new StringBuilder();
builder.Append("SELECT a.[ProductId], [TypeId], [ProductName], [ProductCode], [ShortDescription], [Unit], [Description], ").Append("[SaleStatus], [ImageUrl1], [ImageUrl2], [ImageUrl3], ").Append("[ImageUrl4], [ImageUrl5], [MarketPrice], [HasSKU] ").Append("FROM Hishop_Products a left join Taobao_Products b on a.productid=b.productid WHERE ");
builder.Append("(");
if (query.IncludeOnSales)
{
builder.AppendFormat("SaleStatus = {0} OR ", 1);
}
if (query.IncludeUnSales)
{
builder.AppendFormat("SaleStatus = {0} OR ", 2);
}
if (query.IncludeInStock)
{
builder.AppendFormat("SaleStatus = {0} OR ", 3);
}
builder.Remove(builder.Length - 4, 4);
builder.Append(")");
if (query.IsMakeTaobao.HasValue && (((nullable = query.IsMakeTaobao).GetValueOrDefault() != -1) || !nullable.HasValue))
{
if (query.IsMakeTaobao == 1)
{
builder.AppendFormat(" AND a.ProductId IN (SELECT ProductId FROM Taobao_Products)", new object[0]);
}
else
{
builder.AppendFormat(" AND a.ProductId NOT IN (SELECT ProductId FROM Taobao_Products)", new object[0]);
}
}
if (query.BrandId.HasValue)
{
builder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
}
if (!string.IsNullOrEmpty(query.Keywords))
{
query.Keywords = DataHelper.CleanSearchString(query.Keywords);
string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[0]));
for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
{
builder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[i]));
}
}
if (!string.IsNullOrEmpty(query.ProductCode))
{
builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
}
if (query.CategoryId.HasValue && (query.CategoryId.Value > 0))
{
builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%' )", query.MaiCategoryPath);
}
if (query.StartDate.HasValue)
{
builder.AppendFormat(" AND AddedDate >='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
builder.AppendFormat(" AND AddedDate <='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (!string.IsNullOrEmpty(removeProductIds))
{
builder.AppendFormat(" AND a.ProductId NOT IN ({0})", removeProductIds);
}
builder.AppendFormat(" ORDER BY {0} {1}", query.SortBy, query.SortOrder);
DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Product_GetExportList");
this.database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, builder.ToString());
return this.database.ExecuteDataSet(storedProcCommand);
}
public static DataSet GetExportProducts(AdvancedProductQuery query, bool includeCostPrice, bool includeStock, string removeProductIds)
{
DataSet set = ProductProvider.Instance().GetExportProducts(query, includeCostPrice, includeStock, removeProductIds);
set.Tables[0].TableName = "types";
set.Tables[1].TableName = "attributes";
set.Tables[2].TableName = "values";
set.Tables[3].TableName = "products";
set.Tables[4].TableName = "skus";
set.Tables[5].TableName = "skuItems";
set.Tables[6].TableName = "productAttributes";
return set;
}
private AdvancedProductQuery GetQuery()
{
AdvancedProductQuery query2 = new AdvancedProductQuery();
query2.Keywords = this._productName;
query2.ProductCode = this._productCode;
query2.ProductLineId = this._lineId;
query2.PageSize = this.pager.PageSize;
query2.PageIndex = this.pager.PageIndex;
query2.SaleStatus = ProductSaleStatus.OnSale;
query2.SortOrder = SortAction.Desc;
query2.SortBy = "DisplaySequence";
query2.StartDate = this._startDate;
query2.EndDate = this._endDate;
AdvancedProductQuery entity = query2;
Globals.EntityCoding(entity, true);
return entity;
}
public override DataSet GetExportProducts(AdvancedProductQuery query, bool includeCostPrice, bool includeStock, string removeProductIds)
{
StringBuilder builder = new StringBuilder();
builder.Append("SELECT [ProductId], [TypeId], [ProductName], [ProductCode], [ShortDescription], [Unit], [Description], ").Append("[Title], [Meta_Description], [Meta_Keywords], [SaleStatus], [ImageUrl1], [ImageUrl2], [ImageUrl3], ").Append("[ImageUrl4], [ImageUrl5], [MarketPrice], [LowestSalePrice], [PenetrationStatus], [HasSKU] ").Append("FROM Hishop_Products WHERE ");
builder.Append("(");
if (query.IncludeOnSales)
{
builder.AppendFormat("SaleStatus = {0} OR ", 1);
}
if (query.IncludeUnSales)
{
builder.AppendFormat("SaleStatus = {0} OR ", 2);
}
if (query.IncludeInStock)
{
builder.AppendFormat("SaleStatus = {0} OR ", 3);
}
builder.Remove(builder.Length - 4, 4);
builder.Append(")");
if (query.BrandId.HasValue)
{
builder.AppendFormat(" AND BrandId = {0}", query.BrandId.Value);
}
if (!string.IsNullOrEmpty(query.Keywords))
{
query.Keywords = DataHelper.CleanSearchString(query.Keywords);
string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[0]));
for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
{
builder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[i]));
}
}
if (query.ProductLineId.HasValue && (query.ProductLineId.Value > 0))
{
builder.AppendFormat(" AND LineId={0}", Convert.ToInt32(query.ProductLineId.Value));
}
if (query.PenetrationStatus != PenetrationStatus.NotSet)
{
builder.AppendFormat(" AND PenetrationStatus={0}", (int)query.PenetrationStatus);
}
if (!string.IsNullOrEmpty(query.ProductCode))
{
builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
}
if (query.CategoryId.HasValue && (query.CategoryId.Value > 0))
{
builder.AppendFormat(" AND ( MainCategoryPath LIKE '{0}|%' OR ExtendCategoryPath LIKE '{0}|%' )", query.MaiCategoryPath);
}
if (query.StartDate.HasValue)
{
builder.AppendFormat(" AND AddedDate >='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
builder.AppendFormat(" AND AddedDate <='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (!string.IsNullOrEmpty(removeProductIds))
{
builder.AppendFormat(" AND ProductId NOT IN ({0})", removeProductIds);
}
builder.AppendFormat(" ORDER BY {0} {1}", query.SortBy, query.SortOrder);
DbCommand storedProcCommand = database.GetStoredProcCommand("cp_Product_GetExportList");
database.AddInParameter(storedProcCommand, "sqlPopulate", DbType.String, builder.ToString());
return database.ExecuteDataSet(storedProcCommand);
}
public static DbQueryResult GetExportProducts(AdvancedProductQuery query, string removeProductIds)
{
return ProductProvider.Instance().GetExportProducts(query, removeProductIds);
}
private AdvancedProductQuery GetQuery()
{
AdvancedProductQuery entity = new AdvancedProductQuery {
Keywords = this._productName,
ProductCode = this._productCode,
CategoryId = this._categoryId,
PageSize = this.pager.PageSize,
PageIndex = this.pager.PageIndex,
SaleStatus = ProductSaleStatus.OnSale,
SortOrder = SortAction.Desc,
SortBy = "DisplaySequence",
StartDate = this._startDate,
EndDate = this._endDate,
IncludeInStock = this._includeInStock,
IncludeOnSales = this._includeOnSales,
IncludeUnSales = this._includeUnSales,
IsMakeTaobao = new int?(Convert.ToInt32(this._isMakeTaobao))
};
if (this._categoryId.HasValue)
{
entity.MaiCategoryPath = CatalogHelper.GetCategory(this._categoryId.Value).Path;
}
Globals.EntityCoding(entity, true);
return entity;
}
public abstract DataSet GetExportProducts(AdvancedProductQuery query, bool includeCostPrice, bool includeStock, string removeProductIds);
public abstract DbQueryResult GetExportProducts(AdvancedProductQuery query, string removeProductIds);
public override DbQueryResult GetExportProducts(AdvancedProductQuery query, string removeProductIds)
{
StringBuilder builder = new StringBuilder("");
builder.AppendFormat("PenetrationStatus = 1 AND LineId IN (SELECT LineId FROM Hishop_DistributorProductLines WHERE UserId = {0})", HiContext.Current.User.UserId);
if (!string.IsNullOrEmpty(query.Keywords))
{
query.Keywords = DataHelper.CleanSearchString(query.Keywords);
string[] strArray = Regex.Split(query.Keywords.Trim(), @"\s+");
builder.AppendFormat(" AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[0]));
for (int i = 1; (i < strArray.Length) && (i <= 4); i++)
{
builder.AppendFormat("AND ProductName LIKE '%{0}%'", DataHelper.CleanSearchString(strArray[i]));
}
}
if (query.ProductLineId.HasValue && (query.ProductLineId.Value > 0))
{
builder.AppendFormat(" AND LineId={0}", Convert.ToInt32(query.ProductLineId.Value));
}
if (query.PenetrationStatus != PenetrationStatus.NotSet)
{
builder.AppendFormat(" AND PenetrationStatus={0}", (int)query.PenetrationStatus);
}
if (!string.IsNullOrEmpty(query.ProductCode))
{
builder.AppendFormat(" AND ProductCode LIKE '%{0}%'", DataHelper.CleanSearchString(query.ProductCode));
}
if (query.StartDate.HasValue)
{
builder.AppendFormat(" AND AddedDate >='{0}'", DataHelper.GetSafeDateTimeFormat(query.StartDate.Value));
}
if (query.EndDate.HasValue)
{
builder.AppendFormat(" AND AddedDate <='{0}'", DataHelper.GetSafeDateTimeFormat(query.EndDate.Value));
}
if (!string.IsNullOrEmpty(removeProductIds))
{
builder.AppendFormat(" AND ProductId NOT IN ({0})", removeProductIds);
}
string selectFields = "ProductId, ProductCode, ProductName, ThumbnailUrl40, MarketPrice, SalePrice,(SELECT PurchasePrice FROM Hishop_SKUs WHERE SkuId = p.SkuId) AS PurchasePrice, (SELECT CostPrice FROM Hishop_SKUs WHERE SkuId = p.SkuId) AS CostPrice, Stock, DisplaySequence,LowestSalePrice,PenetrationStatus";
return DataHelper.PagingByRownumber(query.PageIndex, query.PageSize, query.SortBy, query.SortOrder, query.IsCount, "vw_Hishop_BrowseProductList p", "ProductId", builder.ToString(), selectFields);
}
public static DbQueryResult GetExportProducts(AdvancedProductQuery query, string removeProductIds)
{
return new ProductDao().GetExportProducts(query, removeProductIds);
}
