public static VolumeDiscountCollection LoadForGroup(Int32 groupId, int maximumRows, int startRowIndex, string sortExpression)
{
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0)
{
selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
selectQuery.Append(" " + VolumeDiscount.GetColumnNames("ac_VolumeDiscounts"));
selectQuery.Append(" FROM ac_VolumeDiscounts, ac_VolumeDiscountGroups");
selectQuery.Append(" WHERE ac_VolumeDiscounts.VolumeDiscountId = ac_VolumeDiscountGroups.VolumeDiscountId");
selectQuery.Append(" AND ac_VolumeDiscountGroups.GroupId = @groupId");
selectQuery.Append(" AND StoreId = @storeId");
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
database.AddInParameter(selectCommand, "@groupId", System.Data.DbType.Int32, groupId);
database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, Token.Instance.StoreId);
//EXECUTE THE COMMAND
VolumeDiscountCollection results = new VolumeDiscountCollection();
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
VolumeDiscount volumeDiscount = new VolumeDiscount();
VolumeDiscount.LoadDataReader(volumeDiscount, dr);
results.Add(volumeDiscount);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return(results);
}
/// <summary>
/// Get discounts that apply to a specific product for the current user
/// </summary>
/// <param name="productId">ID of the product to check</param>
/// <returns>Discounts that apply to a specific product for the current user.</returns>
private static VolumeDiscountCollection GetProductDiscountsForProduct(int productId)
{
//INITIALIZE THE RETURN SET
VolumeDiscountCollection availableDiscounts = new VolumeDiscountCollection();
//FIRST GET DISCOUNTS APPLIED DIRECTLY TO PRODUCT
VolumeDiscountCollection productDiscounts = VolumeDiscountDataSource.LoadForProduct(productId);
//TEST DISCOUNTS FOR USER
foreach (VolumeDiscount testDiscount in productDiscounts)
{
if (testDiscount.IsValidForUser(Token.Instance.User))
{
availableDiscounts.Add(testDiscount);
}
}
return(availableDiscounts);
}
public static VolumeDiscountCollection LoadForCriteria(string sqlCriteria, int maximumRows, int startRowIndex, string sortExpression)
{
//CREATE THE DYNAMIC SQL TO LOAD OBJECT
StringBuilder selectQuery = new StringBuilder();
selectQuery.Append("SELECT");
if (maximumRows > 0)
{
selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
}
selectQuery.Append(" " + VolumeDiscount.GetColumnNames(string.Empty));
selectQuery.Append(" FROM ac_VolumeDiscounts");
string whereClause = string.IsNullOrEmpty(sqlCriteria) ? string.Empty : " WHERE " + sqlCriteria;
selectQuery.Append(whereClause);
if (!string.IsNullOrEmpty(sortExpression))
{
selectQuery.Append(" ORDER BY " + sortExpression);
}
Database database = Token.Instance.Database;
DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
//EXECUTE THE COMMAND
VolumeDiscountCollection results = new VolumeDiscountCollection();
int thisIndex = 0;
int rowCount = 0;
using (IDataReader dr = database.ExecuteReader(selectCommand))
{
while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
{
if (thisIndex >= startRowIndex)
{
VolumeDiscount volumeDiscount = new VolumeDiscount();
VolumeDiscount.LoadDataReader(volumeDiscount, dr);
results.Add(volumeDiscount);
rowCount++;
}
thisIndex++;
}
dr.Close();
}
return(results);
}
/// <summary>
/// Returns a collection of discounts available for a product for the current user.
/// </summary>
/// <param name="productId">ID of the product to test</param>
/// <returns>A collection of discounts available for a product for the current user.</returns>
public static VolumeDiscountCollection GetAvailableDiscounts(int productId)
{
//CHECK IF THIS IS A GIFT CERTIFICATE PRODUCT
Product p = ProductDataSource.Load(productId);
//DISCOUNTS ONLY AVAILABLE FOR VALID, NON-GIFT CERTIFICATE PRODUCTS
if ((p == null) || (p.IsGiftCertificate))
{
return(new VolumeDiscountCollection());
}
//IF THERE ARE DISCOUNTS AT THE PRODUCT LEVEL WE WILL NOT PROCESS FURTHER
VolumeDiscountCollection productDiscounts = GetProductDiscountsForProduct(productId);
if (productDiscounts.Count > 0)
{
return(productDiscounts);
}
//RETURN ANY AVAILABLE CATEGORY DISCOUNTS
return(GetCategoryDiscountsForProduct(productId));
}
/// <summary>
/// Gets a dictionary of potential discounts for the basket.
/// </summary>
/// <param name="basket">Basket to check for potential discounts</param>
/// <param name="productsToExclude">Products that should not be considered for discounts.</param>
/// <param name="groupingMode">Grouping mode in use</param>
/// <returns>A dictionary of potential discounts for the order.</returns>
private static Dictionary <int, List <PotentialDiscount> > GetPotentialDiscounts(Basket basket, List <int> productsToExclude, GroupingMode groupingMode)
{
//INITIALIZE THE RETURN VALUE
Dictionary <int, List <PotentialDiscount> > potentialDiscounts = new Dictionary <int, List <PotentialDiscount> >();
//KEEP TRACK OF PRODUCTS TO EXCLUDE FROM GLOBAL DISCOUNTS
List <int> excludeGlobal = new List <int>();
//BUILD CRITERIA FOR PRODUCTS TO EXCLUDE FROM QUERY
string excludedProducts = string.Empty;
if (productsToExclude.Count > 0)
{
if (productsToExclude.Count == 1)
{
excludedProducts = " AND ProductId <> " + productsToExclude[0].ToString();
}
else
{
excludedProducts = " AND ProductId NOT IN (" + AlwaysConvert.ToList(",", productsToExclude.ToArray()) + ")";
}
//ALSO EXCLUDE THESE PRODUCTS FROM GLOBAL DISCOUNTS
excludeGlobal.AddRange(productsToExclude);
}
//FIND ALL DISCOUNTS THAT ARE ASSOCIATED WITH PRODUCTS IN THE BASKET
StringBuilder categorySql = new StringBuilder();
categorySql.Append("SELECT DISTINCT CP.ParentId, CP.ParentLevel, CVD.VolumeDiscountId");
if (groupingMode == GroupingMode.Product)
{
categorySql.Append(", CN.CatalogNodeId");
}
categorySql.Append(" FROM (ac_BasketItems BI INNER JOIN ac_CatalogNodes CN ON BI.ProductId = CN.CatalogNodeId");
categorySql.Append(" INNER JOIN ac_CategoryParents CP ON CN.CategoryId = CP.CategoryId");
categorySql.Append(" INNER JOIN ac_CategoryVolumeDiscounts CVD ON CP.ParentId = CVD.CategoryId)");
categorySql.Append(" WHERE BI.BasketId = @basketId ");
categorySql.Append(" AND CN.CatalogNodeTypeId = 1");
categorySql.Append(excludedProducts);
//ORDER FROM THE MOST SPECIFIC CATEGORY TO THE LEAST (PARENTLEVEL)
categorySql.Append(" ORDER BY CP.ParentLevel DESC");
if (groupingMode == GroupingMode.Product)
{
categorySql.Append(", CN.CatalogNodeId ASC");
}
//CREATE THE COMMAND
Database database = Token.Instance.Database;
DbCommand command = database.GetSqlStringCommand(categorySql.ToString());
database.AddInParameter(command, "@basketId", System.Data.DbType.Int32, basket.BasketId);
DataSet allDiscounts = database.ExecuteDataSet(command);
//LOOP ALL DISCOUNTS, BUILD LIST OF POTENTIAL DISCOUNTS THAT COULD APPLY TO USER
User user = basket.User;
foreach (DataRow row in allDiscounts.Tables[0].Rows)
{
//GET THE DISCOUNTED OBJECT (EITHER PRODUCT OR CATEGORY LEVEL)
int key;
if (groupingMode == GroupingMode.Product)
{
key = (int)row[3];
}
else
{
key = Convert.ToInt32((byte)row[1]);
}
//GET THE LEVEL OF THE CATEGORY (DETERMINES PRECEDENCE)
int categoryLevel = Convert.ToInt32((byte)row[1]);
//FIND THE LIST OF POTENTIAL DISCOUNTS FOR THIS KEY
if (!potentialDiscounts.ContainsKey(key))
{
potentialDiscounts[key] = new List <PotentialDiscount>();
}
List <PotentialDiscount> discountGroup = potentialDiscounts[key];
//DECIDE WHETHER THIS DISCOUNT HAS ENOUGH PRECDENCE TO CHECK
bool checkDiscount = true;
if (discountGroup.Count > 0)
{
//THE CURRENT ROW COULD HAVE THE SAME LEVEL AS THE DISCOUNTS ALREADY FOUND
if (categoryLevel < discountGroup[0].CategoryLevel)
{
checkDiscount = false;
}
//WE DO NOT HAVE TO CHECK FOR LEVEL BEING GREATER, BECAUSE
//THE QUERY INCLUDES PARENT LEVEL IN THE ORDERBY CLAUSE
}
if (checkDiscount)
{
//THIS COULD BE A VALID DISCOUNT
int volumeDiscountId = (int)row[2];
//CHECK FOR USER EXCLUSIONS
VolumeDiscount v = VolumeDiscountDataSource.Load(volumeDiscountId);
if ((v != null) && (v.IsValidForUser(user)))
{
//THE DISCOUNT IS VALID FOR THIS USER
//ADD TO THE LIST OF POTENTIALS
PotentialDiscount pd = new PotentialDiscount();
pd.CategoryId = (int)row[0];
pd.CategoryLevel = categoryLevel;
pd.VolumeDiscountId = volumeDiscountId;
if (groupingMode == GroupingMode.Product)
{
pd.ProductId = key;
//SINCE THERE IS A CATEGORY DISCOUNT, WE DO
//NOT WANT TO USE ANY GLOBAL DISCOUNTS FOR THIS PRODUCT
excludeGlobal.Add(key);
}
discountGroup.Add(pd);
}
}
}
List <int> globalDiscountProducts = new List <int>();
if (groupingMode == GroupingMode.Product)
{
//CHECK WHETHER ANY PRODUCTS REMAIN IN ORDER THAT ARE ELIGIBLE
//FOR GLOBAL DISCOUNTS
foreach (BasketItem item in basket.Items)
{
//MAKE SURE THIS IS A PRODUCT AND IS NOT EXCLUDED FROM GLOBAL DISCOUNT
if ((item.ProductId > 0) && (excludeGlobal.IndexOf(item.ProductId) < 0))
{
//ADD THIS PRODUCT TO THE GLOBAL DISCOUNT LIST (IF NOT ALREADY THERE)
if (globalDiscountProducts.IndexOf(item.ProductId) < 0)
{
globalDiscountProducts.Add(item.ProductId);
}
}
}
}
//ARE WE CALCULATING DISCOUNTS USING CATEGORY GROUPING MODE?
//OR ARE THERE ANY PRODUCTS THAT COULD HAVE GLOBAL DISCOUNTS?
if (groupingMode == GroupingMode.Category || globalDiscountProducts.Count > 0)
{
//FIND ANY GLOBAL DISCOUNTS
VolumeDiscountCollection globalDiscounts = VolumeDiscountDataSource.LoadGlobal();
if (globalDiscounts.Count > 0)
{
if (groupingMode == GroupingMode.Product)
{
foreach (int productId in globalDiscountProducts)
{
potentialDiscounts[productId] = new List <PotentialDiscount>();
List <PotentialDiscount> discountGroup = potentialDiscounts[productId];
foreach (VolumeDiscount v in globalDiscounts)
{
//VERIFY USER RESTRICTION ON GLOBAL DISCOUNT
if (v.IsValidForUser(user))
{
PotentialDiscount pd = new PotentialDiscount();
pd.CategoryId = 0;
pd.CategoryLevel = 0;
pd.VolumeDiscountId = v.VolumeDiscountId;
pd.ProductId = productId;
discountGroup.Add(pd);
}
}
}
}
else
{
potentialDiscounts[0] = new List <PotentialDiscount>();
List <PotentialDiscount> discountGroup = potentialDiscounts[0];
foreach (VolumeDiscount v in globalDiscounts)
{
//VERIFY USER RESTRICTION ON GLOBAL DISCOUNT
if (v.IsValidForUser(user))
{
PotentialDiscount pd = new PotentialDiscount();
pd.CategoryId = 0;
pd.CategoryLevel = 0;
pd.VolumeDiscountId = v.VolumeDiscountId;
pd.ProductId = 0;
discountGroup.Add(pd);
}
}
}
}
}
//REMOVE ANY ENTRIES FROM THE LIST THAT HAVE NO DISCOUNTS
List <int> emptyKeys = new List <int>();
foreach (int key in potentialDiscounts.Keys)
{
if (potentialDiscounts[key].Count == 0)
{
emptyKeys.Add(key);
}
}
foreach (int key in emptyKeys)
{
potentialDiscounts.Remove(key);
}
//RETURN POTENTIAL DISCOUNTS FOR BASKET
return(potentialDiscounts);
}
/// <summary>
/// Calculates any product specific discounts for the basket
/// </summary>
/// <param name="basket">Basket to calculate discounts for</param>
/// <param name="productsToExclude">Any products to exclude from discounting</param>
/// <returns>The total discount applied to the basket</returns>
private static LSDecimal CalculateProductDiscounts(Basket basket, List <int> productsToExclude)
{
LSDecimal totalDiscount = 0;
//GET ALL PRODUCTS FROM THE BASKET THAT HAVE DISCOUNTS DIRECTLY ASSOCIATED
//TODO: UPDATE THE METHOD BELOW TO FACTOR IN THE COMBINE VARIANTS IN LINE ITEM DISCOUNT MODE SETTING
DiscountedBasketProduct[] discountedItems = DiscountCalculator.GetDiscountedBasketProducts(basket.BasketId, productsToExclude);
if (discountedItems != null && discountedItems.Length > 0)
{
//EVALUATE EACH ITEM TO SEE WHETHER THE PRODUCT DISCOUNT APPLIES
foreach (DiscountedBasketProduct discountedProduct in discountedItems)
{
VolumeDiscount appliedDiscount = null;
LSDecimal discountAmount = -1;
//LOOP ALL AVAILABLE DISCOUNTS AND FIND THE BEST ONE
VolumeDiscountCollection availableDiscounts = VolumeDiscountDataSource.LoadForProduct(discountedProduct.ProductId);
foreach (VolumeDiscount testDiscount in availableDiscounts)
{
if (testDiscount.IsValidForUser(basket.User))
{
LSDecimal tempDiscountAmount = testDiscount.CalculateDiscount(discountedProduct.Quantity, discountedProduct.Quantity, discountedProduct.ExtendedPrice, discountedProduct.ExtendedPrice);
if (tempDiscountAmount > discountAmount)
{
discountAmount = tempDiscountAmount;
appliedDiscount = testDiscount;
}
}
}
//CHECK WHETHER A DISCOUNT APPLIES TO THIS PRODUCT FOR THIS USER
if (appliedDiscount != null)
{
//productsToExclude.Add(discountedProduct.ProductId);
List <BasketItem> newItems = new List <BasketItem>();
//LOOP ALL ITEMS IN BASKET, CALCULATE DISCOUNT FOR MATCHING PRODUCTS
foreach (BasketItem basketItem in basket.Items)
{
if (basketItem.ProductId == discountedProduct.ProductId)
{
//THIS IS A DISCOUNTED PRODUCT, CREATE THE DISCOUNT LINE ITEM
discountAmount = appliedDiscount.CalculateDiscount(basketItem.Quantity, discountedProduct.Quantity, basketItem.ExtendedPrice, discountedProduct.ExtendedPrice);
if (discountAmount > 0)
{
//DISCOUNT AMOUNT SHOULD NOT BE GREATER THEN PARENT ITEM TOTAL
if (discountAmount > basketItem.ExtendedPrice)
{
discountAmount = basketItem.ExtendedPrice;
}
//DISCOUNT MUST BE ADJUSTED FOR OPTIONS THAT ARE GROUPED
BasketItem discountLineItem = new BasketItem();
discountLineItem.BasketId = basket.BasketId;
discountLineItem.OrderItemType = OrderItemType.Discount;
discountLineItem.ParentItemId = basketItem.BasketItemId;
discountLineItem.BasketShipmentId = basketItem.BasketShipmentId;
discountLineItem.Name = appliedDiscount.Name;
discountLineItem.Sku = appliedDiscount.VolumeDiscountId.ToString();
discountLineItem.Price = (-1 * discountAmount);
discountLineItem.Quantity = 1; // basketItem.Quantity;
discountLineItem.TaxCodeId = basketItem.TaxCodeId;
discountLineItem.Shippable = basketItem.Shippable;
discountLineItem.Save();
newItems.Add(discountLineItem);
totalDiscount += discountAmount;
}
}
}
//ADD ANY NEW ITEMS TO THE BASKET COLLECTION
foreach (BasketItem basketItem in newItems)
{
basket.Items.Add(basketItem);
}
//AT LEAST ONE DISCOUNT WAS APPLICABLE TO THIS USER
//ADD THIS PRODUCT TO EXCLUDE LIST
if (productsToExclude.IndexOf(discountedProduct.ProductId) < 0)
{
productsToExclude.Add(discountedProduct.ProductId);
}
}
}
}
return(totalDiscount);
}
/// <summary>
/// Get discounts that apply to a product by virtue of category assignment.
/// </summary>
/// <param name="productId">The product to check for category discounts</param>
/// <returns>The discounts that apply to a product</returns>
private static VolumeDiscountCollection GetCategoryDiscountsForProduct(int productId)
{
//FIND ALL PRODUCTS THAT ARE A DESCENDANT OF THE CATEGORY
StringBuilder categorySql = new StringBuilder();
categorySql.Append("SELECT DISTINCT CP.ParentLevel,CVD.VolumeDiscountId");
categorySql.Append(" FROM ((ac_CatalogNodes CN INNER JOIN ac_CategoryParents CP ON CN.CategoryId = CP.CategoryId)");
categorySql.Append(" INNER JOIN ac_CategoryVolumeDiscounts CVD ON CP.ParentId = CVD.CategoryId)");
categorySql.Append(" WHERE CN.CatalogNodeId = @productId");
categorySql.Append(" AND CN.CatalogNodeTypeId = 1");
categorySql.Append(" ORDER BY CP.ParentLevel DESC");
//EXECUTE THE QUERY
Database database = Token.Instance.Database;
DbCommand command = database.GetSqlStringCommand(categorySql.ToString());
database.AddInParameter(command, "@productId", System.Data.DbType.Int32, productId);
//BUILD LIST OF VOLUME DISCOUNT IDS APPLICABLE FOR THE USER
User user = Token.Instance.User;
List <int> volumeDiscountIds = new List <int>();
int lastLevel = -1;
bool levelChanged = false;
using (IDataReader dr = database.ExecuteReader(command))
{
while (dr.Read() && !levelChanged)
{
int thisLevel = (int)dr.GetByte(0);
int volumeDiscountId = dr.GetInt32(1);
VolumeDiscount v = VolumeDiscountDataSource.Load(volumeDiscountId);
if (v.IsValidForUser(user))
{
if ((lastLevel > -1) && (lastLevel != thisLevel))
{
levelChanged = true;
}
else
{
volumeDiscountIds.Add(dr.GetInt32(1));
lastLevel = thisLevel;
}
}
}
dr.Close();
}
//INITIALIZE THE RETURN SET
VolumeDiscountCollection discounts = new VolumeDiscountCollection();
//DID WE FIND DISCOUNTS ON A CATEGORY?
if (volumeDiscountIds.Count > 0)
{
//BUILD THE COLLECTION OF DISCOUNTS
foreach (int id in volumeDiscountIds)
{
VolumeDiscount v = VolumeDiscountDataSource.Load(id);
if (v != null)
{
discounts.Add(v);
}
}
}
else
{
//NO DISCOUNT FOUND ON A CATEGORY, RETURN ANY GLOBAL DISCOUNTS
VolumeDiscountCollection globalDiscounts = VolumeDiscountDataSource.LoadGlobal();
foreach (VolumeDiscount v in globalDiscounts)
{
if (v.IsValidForUser(user))
{
discounts.Add(v);
}
}
}
//RETURN ANY CATEGORY DISCOUNTS FOUND
return(discounts);
}
){kind=link}
){kind=link}