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); }