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