/// <summary>
/// 根据商品编号获取SKU中最低的价格
/// </summary>
/// <param name="productNo">商品编号，多个以逗号隔开</param>
/// <returns></returns>
public static List <SpfSkuPriceModel> GetSubjectProductSkuPrice(List <string> productNo)
{
List <SpfSkuPriceModel> list = new List <SpfSkuPriceModel>();
StringBuilder pNo = new StringBuilder();
if (productNo != null && productNo.Count() > 0)
{
int num = 0;
foreach (var item in productNo)
{
num++;
pNo.Append("'" + item + "'");
if (num < productNo.Count())
{
pNo.Append(",");
}
}
}
if (pNo != null)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT se.ProductNo,se.SkuNo ,sp.MarketPrice ,sp.StandardPrice ,sp.GoldPrice ,sp.PlatinumPrice , ");
sql.Append("sp.DiamondPrice ,sp.OutletPrice ,se.IsPromotion,sp.PromotionPrice ,sp.DiscountShangpin ,sp.DiscountOutlet,se.PcSaleState ");
sql.Append("FROM dbo.SpfSkuExtend(NOLOCK) AS se ");
sql.Append("INNER JOIN dbo.SpfSkuPrice(NOLOCK) AS sp ON se.SkuNo = sp.SkuNo ");
sql.Append("WHERE se.DataState = 1 AND se.DisabledState = 0 AND se.PcSaleState IN (2,3) AND se.PcShowState = 1 AND se.ProductNo IN ({0}) ");
sql.Append("ORDER BY sp.OutletPrice ASC,se.SkuId DESC");
string tempSql = string.Format(sql.ToString(), pNo);
try
{
DataTable dt = SqlHelper.QueryTable(tempSql);
if (dt != null && dt.Rows.Count > 0)
{
for (var i = 0; i < dt.Rows.Count; i++)
{
SpfSkuPriceModel m = new SpfSkuPriceModel();
m.ProductNo = dt.Rows[i]["ProductNo"].ToString();
m.SkuNo = dt.Rows[i]["SkuNo"].ToString();
m.MarketPrice = Convert.ToDecimal(dt.Rows[i]["MarketPrice"].ToString());
m.StandardPrice = Convert.ToDecimal(dt.Rows[i]["StandardPrice"].ToString());
m.OutletPrice = Convert.ToDecimal(dt.Rows[i]["OutletPrice"].ToString());
m.PcSaleState = Convert.ToInt32(dt.Rows[i]["PcSaleState"].ToString());
list.Add(m);
}
}
if (list != null && list.Count() > 0)
{
return(list);
}
}
catch (Exception ex)
{
log.Error("ERROR:根据活动编号获取活动下所有有效商品:" + ex.Message);
}
}
return(null);
}
/// <summary>
/// 根据活动编号获取活动下所有有效商品
/// </summary>
/// <param name="subjectNo">活动编号</param>
/// <param name="type">1折扣 2元</param>
/// <returns></returns>
private static List <SubjectProductM> GetSubjectProductData(string subjectNo, int type)
{
List <SubjectProductM> productList = new List <SubjectProductM>();
StringBuilder sb = new StringBuilder();
sb.Append("SELECT pe.ProductNo ");
sb.Append(" FROM SWfsSubjectProductRef (NOLOCK) AS pr ");
sb.Append(" INNER JOIN dbo.SpfProductExtend (NOLOCK) AS pe ON pr.ProductNo = pe.ProductNo ");
sb.Append(" WHERE pe.DataState = 1 ");
sb.Append(" AND pe.[Status] = 1 AND pe.TypeFlag = 2 AND pe.IsOutside = 1 AND pr.TypeFlag = 0 ");
sb.Append(" AND pr.IsShow = 1 AND pr.ShowTime <= GETDATE()");
sb.Append(" AND ( SELECT COUNT(1) FROM dbo.SpfSkuExtend(NOLOCK) WHERE pr.ProductNo = ProductNo ");
sb.Append(" AND DataState = 1 AND DisabledState = 0 AND PcSaleState = 2 AND PcShowState = 1 ) >= 1 ");
sb.Append(" AND EXISTS (SELECT 1 FROM dbo.SWfsSubjectCategory(NOLOCK) WHERE pr.CategoryNo = CategoryNo AND SubjectNo = '{0}' ) ");
sb.Append(" ORDER BY pr.SortNo ASC , pr.DateCreate DESC");
string sql = string.Format(sb.ToString(), subjectNo);
try
{
DataTable dt = SqlHelper.QueryTable(sql);
if (dt != null && dt.Rows.Count > 0)
{
for (var i = 0; i < dt.Rows.Count; i++)
{
SubjectProductM p = new SubjectProductM();
p.ProductNo = dt.Rows[i][0].ToString();
productList.Add(p);
}
#region 获取SKU最低奥莱价格
List <SpfSkuPriceModel> skuPriceData = new List <SpfSkuPriceModel>();
List <string> productArrayNo = new List <string>();
productArrayNo = (from p in productList select p.ProductNo).Distinct().ToList();
if (productArrayNo != null && productArrayNo.Count() > 0)
{
#region 最低价格
skuPriceData = GetSubjectProductSkuPrice(productArrayNo);
#endregion
}
#endregion
foreach (var item in productList)
{
#region 获取SKU最低奥莱价格
if (skuPriceData != null && skuPriceData.Count() > 0)
{
SpfSkuPriceModel priceModel = skuPriceData.Where(a => a.ProductNo.Equals(item.ProductNo)).FirstOrDefault();
if (priceModel != null)
{
decimal marketprice = priceModel.MarketPrice == 0 ? 1 : priceModel.MarketPrice;
item.MarketPrice = priceModel.MarketPrice;
item.OutletPrice = priceModel.OutletPrice;
item.DiscountRate = (priceModel.OutletPrice / marketprice) * 10;
}
}
#endregion
}
if (productList != null && productList.Count() > 0)
{
if (type == 1)
{
productList = productList.Distinct(new ComparerSubjectProduct()).OrderBy(o => o.DiscountRate).ToList();
}
else if (type == 2)
{
productList = productList.Distinct(new ComparerProductData()).OrderBy(o => o.OutletPrice).ToList();
}
}
}
}
catch (Exception ex)
{
log.Error("ERROR:根据活动编号获取活动下所有有效商品:" + ex.Message);
}
return(productList);
}