public static VmScenario Build(SmScenario entity)
{
return(new VmScenario
{
ScenarioId = entity.ScenarioId,
Week = entity.Week,
ScheduleWeekMin = entity.ScheduleWeekMin,
ScheduleWeekMax = entity.ScheduleWeekMax,
ScheduleStageMin = entity.ScheduleStageMin,
ScheduleStageMax = entity.ScheduleStageMax,
StageMax = entity.StageMax,
StageOffsetMax = entity.StageOffsetMax,
PriceFloor = entity.PriceFloor,
CustomerId = entity.OrganisationId,
ScenarioName = entity.ScenarioName,
ScheduleMask = entity.ScheduleMask,
MarkdownCountStartWeek = entity.MarkdownCountStartWeek,
DefaultMarkdownType = entity.DefaultMarkdownType,
AllowPromoAsMarkdown = entity.AllowPromoAsMarkdown,
MinimumPromoPercentage = entity.MinimumPromoPercentage
});
}
public async Task<int> Create(string name, int week, int scenarioMask, int senarioWeekMin,
int scenarioWeekMax, int markdownCountStartWeek, int defaultMarkdownType,
DecisionState defaultDecisionState, bool allowPromoAsMarkdown, decimal minimumPromoPercentage,
int organisationId, int userId, List<int> productIds, List<int> hierarchyIds)
{
var scenario = SmScenario.Build(name, week, scenarioMask, senarioWeekMin,
scenarioWeekMax, markdownCountStartWeek, defaultMarkdownType,
defaultDecisionState, allowPromoAsMarkdown, minimumPromoPercentage,
organisationId,userId );
var scenarioId = await _scenarioRepository.CreateOrUpdate(scenario.Week, scenario.ScheduleWeekMin,
scenario.ScheduleWeekMax, scenario.ScheduleStageMin,
scenario.ScheduleStageMax, scenario.StageMax, scenario.StageOffsetMax, scenario.PriceFloor,
scenario.OrganisationId,scenario.CreatedBy, scenario.ScenarioName, scenario.ScheduleMask,
scenario.MarkdownCountStartWeek, scenario.DefaultMarkdownType,
scenario.DefaultDecisionState);
if (hierarchyIds.Any())
{
var filters = hierarchyIds
.Select(x => new ScenarioHierarchyFilter {HierarchyId = x, ScenarioId = scenarioId})
.ToList();
await _scenarioHierarchyFilterRepository.Create(filters);
}
if (productIds.Any())
{
var products = productIds
.Select(x => new ScenarioProductFilter { ProductId = x, ScenarioId = scenarioId })
.ToList();
await _scenarioProductFilterRepository.Create(products);
}
return scenarioId;
}
public SmCalcProduct Calculate(
SmScenario scenario,
int modelId,
int revisionId,
List <SmDenseSchedule> schedules,
Dictionary <int, SmDecayHierarchy> decayHierarchies,
Dictionary <int, SmElasticityHierarchy> elasticityHierarchies,
SmProduct product,
List <decimal> revisedDiscounts = null,
CancellationToken cancellationToken = default(CancellationToken))
{
// Calculate the current markdown depth and ladder depth
var depth = GetDiscountLadderDepth(product);
var result = new SmCalcProduct(scenario, modelId, product, schedules, depth);
// Return OK if there are no schedules for this product
if (!schedules.Any())
{
_logger.Warning("No schedules for {ProductId}", product.ProductId);
return(result.Ok(ProductState.NoSchedules));
}
// Return Fatal recommendation for bad CSPs
if (product.CurrentSellingPrice <= 0)
{
_logger.Warning("Product {ProductId} CSP is <= 0", product.ProductId);
return(result.Fatal(ProductState.InvalidCsp));
}
// Return Fatal if we can't resolve the decay hierarchy
if (!decayHierarchies.TryGetValue(product.HierarchyId, out SmDecayHierarchy decayHierarchy))
{
_logger.Warning("Can't find Decay Hierarchy by HierarchyId {HierarchyId} for Product {ProductId}", product.HierarchyId, product.ProductId);
return(result.Fatal(ProductState.InvalidDecayHierarchy));
}
// Return Fatal if we can't resolve the elasticity hierarchy
if (!elasticityHierarchies.TryGetValue(product.HierarchyId, out SmElasticityHierarchy elasticityHierarchy))
{
_logger.Warning("Can't find Elasticity Hierarchy by HierarchyId {HierarchyId} for Product {ProductId}", product.HierarchyId, product.ProductId);
return(result.Fatal(ProductState.InvalidElasticityHierarchy));
}
var recommendations = new List <SmCalcRecommendation>();
foreach (var schedule in schedules)
{
// Apply the product mask to this schedule and skip of not aligned
if (product.ProductScheduleMask != null && (schedule.ScheduleNumber & product.ProductScheduleMask.Value) != 0)
{
result.ScheduleProductMaskFilterCount++;
continue;
}
// Skip products where the max markdown is exceeded
if (product.ProductMaxMarkdown != null && product.ProductMaxMarkdown.Value >= schedule.MarkdownCount)
{
result.ScheduleMaxMarkdownFilterCount++;
continue;
}
if (product.ProductHasExceededFlowlineThreshold == 1)
{
result.ScheduleExceededFlowlineThresholdFilterCount++;
continue;
}
var crossProduct = GetCrossProduct(schedule, product.PriceLadder);
result.ScheduleCrossProductCount = crossProduct.Length;
foreach (var path in crossProduct)
{
var prices = path.Prices.Select(x => x ?? 0).ToArray();
var recommendation = new SmCalcRecommendation(scenario, schedule, prices, revisionId);
var calculateResult = CalculatePricePath(
product: ref result,
recommendation: ref recommendation,
scenario: scenario,
modelId: modelId,
revisionId: revisionId,
decayHierarchy: decayHierarchy,
elasticityHierarchy: elasticityHierarchy,
priceLadder: product.PriceLadder,
scheduleId: schedule.ScheduleNumber,
schedulePricePath: path);
if (calculateResult)
{
// Get top 10 by Total Revenue
recommendations.InsertAfter(recommendation, 10, x => recommendation.TotalRevenue > x.TotalRevenue);
}
cancellationToken.ThrowIfCancellationRequested();
}
}
// For initial runs, calculate CSP (where revision id = 0)
if (revisionId == 0)
{
var noChangeSchedule = SmDenseSchedule.NoMarkdowns(scenario.ScheduleWeekMin, scenario.ScheduleWeekMax);
var cspPricePath = SmSchedulePricePath.Build(noChangeSchedule.WeekMin, noChangeSchedule.WeekMax, noChangeSchedule.MarkdownWeeks, product.PriceLadder.Type, product.PriceLadder.Values);
var prices = cspPricePath.Prices.Select(x => x ?? 0).ToArray();
var cspRecommendation = new SmCalcRecommendation(scenario, noChangeSchedule, prices, revisionId, isCsp: true);
var cspResult = CalculatePricePath(
product: ref result,
recommendation: ref cspRecommendation,
scenario: scenario,
modelId: modelId,
revisionId: revisionId,
decayHierarchy: decayHierarchy,
elasticityHierarchy: elasticityHierarchy,
priceLadder: product.PriceLadder,
scheduleId: noChangeSchedule.ScheduleNumber,
schedulePricePath: cspPricePath);
if (cspResult)
{
recommendations.InsertAfter(cspRecommendation, recommendations.Count + 1, x => cspRecommendation.TotalRevenue > x.TotalRevenue, true);
}
result.ScheduleCount++;
}
if (!recommendations.Any())
{
_logger.Warning("No recommendations made for {ProductId}", product.ProductId);
return(result.Ok());
}
// Set rank, store and set stats
var results = new List <SmCalcRecommendation>();
foreach (var ordered in recommendations.Select((x, i) => new { recomendation = x, index = i }))
{
var recommendation = ordered.recomendation;
recommendation.Rank = recommendations.Count - ordered.index;
results.Add(recommendation);
}
ExpandProjectionWeeks(ref result, ref results, scenario, revisedDiscounts);
return(result.Ok(results));
}
public bool CalculatePricePath(
ref SmCalcProduct product,
ref SmCalcRecommendation recommendation,
SmScenario scenario,
int modelId,
int revisionId,
SmDecayHierarchy decayHierarchy,
SmElasticityHierarchy elasticityHierarchy,
SmProductPriceLadder priceLadder,
int scheduleId,
SmSchedulePricePath schedulePricePath)
{
var scheduleStageMax = scenario.ScheduleStageMax;
var currentMarkdownType = product.CurrentMarkdownType;
var accumulatedMarkdownCount = product.CurrentMarkdownCount;
var totalRevenue = 0M;
var totalCost = 0M;
var totalMarkdownCost = 0M;
var accumulatedMarkdownCountOffset = 0;
var previousPrice = product.CurrentSellingPrice;
var previousQuantity = product.CurrentSalesQuantity;
var accumulatedStockChange = 0;
var projections = new List <SmCalcRecommendationProjection>();
var weeks = schedulePricePath.Weeks;
var firstWeek = schedulePricePath.Weeks[0];
var pricePath = schedulePricePath.Prices;
var salesFlexFactors = product.SalesFlexFactor;
for (var week = 0; week < weeks.Length; week++)
{
// TODO write out price change
var priceChange = pricePath[week];
var flexFactor = salesFlexFactors[week];
var weekMarkdownConstraint = product.MarkdownTypeConstraint[week];
var weekMinimumRelativePercentagePriceChange = product.MinimumRelativePercentagePriceChange[week];
var weekMinDiscountNew = product.MinDiscountsNew[week];
var weekMinDiscountFurther = product.MinDiscountsFurther[week];
var weekMaxDiscountNew = product.MaxDiscountsNew[week];
var weekMaxDiscountFurther = product.MaxDiscountsFurther[week];
var price = product.CurrentSellingPrice;
var currentCostPrice = product.CurrentCostPrice;
if (priceChange != null)
{
switch (priceLadder.Type)
{
case SmPriceLadderType.Percent:
price = product.OriginalSellingPrice * (1 - priceChange.Value);
break;
case SmPriceLadderType.Fixed:
price = product.OriginalSellingPrice - (1 - priceChange.Value);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
if (price > product.CurrentSellingPrice)
{
product.HighPredictionCount++;
return(false);
}
// A change in price advances stage and resets
var elasticity = 0.0M;
if (previousPrice != price)
{
accumulatedMarkdownCount++;
accumulatedMarkdownCountOffset = 0;
var absolutePriceChange = previousPrice - price;
var relativePriceChange = (previousPrice - price) / previousPrice;
if (absolutePriceChange < product.MinimumAbsolutePriceChange)
{
product.MinimumAbsolutePriceChangeNotMetCount++;
return(false);
}
if (absolutePriceChange < product.MinimumAbsolutePriceChange)
{
product.MinimumAbsolutePriceChangeNotMetCount++;
return(false);
}
if (relativePriceChange < weekMinimumRelativePercentagePriceChange)
{
product.MinimumRelativePercentagePriceChangeNotMetCount++;
return(false);
}
currentMarkdownType = (accumulatedMarkdownCount == 1)
? MarkdownType.New
: MarkdownType.Further;
if (!weekMarkdownConstraint.HasFlag(currentMarkdownType))
{
product.InvalidMarkdownTypeCount++;
return(false);
}
switch (currentMarkdownType)
{
case MarkdownType.New:
if (weekMinDiscountNew > priceChange || weekMaxDiscountNew < priceChange)
{
product.DiscountPercentageOutsideAllowedRangeCount++;
return(false);
}
else
{
break;
}
case MarkdownType.Further:
if (weekMinDiscountFurther > priceChange || weekMaxDiscountFurther < priceChange)
{
product.DiscountPercentageOutsideAllowedRangeCount++;
return(false);
}
else
{
break;
}
default:
throw new ArgumentOutOfRangeException();;
}
// Use the elasticity for this calculation
if (accumulatedMarkdownCount > 0 && accumulatedMarkdownCountOffset == 0)
{
var elasticityStage = Math.Min(accumulatedMarkdownCount, Math.Min(elasticityHierarchy.MaxStage, scheduleStageMax));
elasticity = elasticityHierarchy.TryGetValue(elasticityStage, out SmElasticity e) ? e.PriceElasticity : 1.0M;
}
}
else
{
currentMarkdownType = accumulatedMarkdownCount == 0 && product.CurrentSellingPrice >= product.OriginalSellingPrice
? MarkdownType.FullPrice
: MarkdownType.Existing;
if (!weekMarkdownConstraint.HasFlag(currentMarkdownType))
{
product.InvalidMarkdownTypeCount++;
return(false);
}
}
// Get decay
var decay = 1.0M;
if (accumulatedMarkdownCountOffset > 0)
{
var decayStage = Math.Min(accumulatedMarkdownCount, Math.Min(decayHierarchy.MaxStage, scheduleStageMax));
decay = decayHierarchy.TryGetValue(decayStage, accumulatedMarkdownCountOffset, out SmDecay d) ? d.Decay : 1.0M;
}
// Calculate the predicted quantity sold
var predictedQuantity = (int)Math.Round(accumulatedMarkdownCount == product.CurrentMarkdownCount
? previousQuantity * decay
: previousQuantity * decay * (1 - (((previousPrice - price) / previousPrice) * elasticity)));
// Calculate projected stock
var stock = Math.Max(product.CurrentStock - accumulatedStockChange, 0);
// Ensure predicted quantity sold is non-negative
var adjustedQuantity = Math.Max(predictedQuantity, 0) * flexFactor;
// Ensure predicted quantity sold is not more than available stock
var quantity = Math.Min(stock, adjustedQuantity);
// Calculate Metrics
var revenue = price * quantity;
var cost = currentCostPrice * quantity;
var markdownCost = (previousPrice - price) * stock;
projections.Add(new SmCalcRecommendationProjection
{
Week = firstWeek + week,
Discount = priceChange ?? product.CurrentMarkdownDepth,
Price = price,
Quantity = (int)quantity,
Revenue = revenue,
Stock = stock,
MarkdownCost = markdownCost,
AccumulatedMarkdownCount = accumulatedMarkdownCount,
MarkdownCount = accumulatedMarkdownCount - product.CurrentMarkdownCount,
Decay = decay,
Elasticity = elasticity,
MarkdownType = currentMarkdownType
});
totalRevenue += revenue;
totalCost += cost;
totalMarkdownCost += markdownCost;
previousPrice = price;
previousQuantity = (int)quantity;
accumulatedStockChange += (int)quantity;
accumulatedMarkdownCountOffset++;
// TODO test accumulatedStockChange > CurrentStock
if (totalRevenue < 0)
{
product.NegativeRevenueCount++;
return(false);
}
}
var terminalStock = Math.Max((product.CurrentStock - accumulatedStockChange), 0);
var sellThroughTarget = product.CurrentStock - (product.CurrentStock * product.SellThroughTarget);
var sellThroughTerminalStock = terminalStock == 0 ? sellThroughTarget : terminalStock;
var sellThroughRate = product.SellThroughTarget > 0 ? (sellThroughTarget / sellThroughTerminalStock) : 0;
// Assign values
recommendation.PricePath = pricePath.Select(x => x ?? 0).ToArray();
recommendation.IsCsp = (schedulePricePath.MarkdownCount == 0);
recommendation.TotalMarkdownCount = accumulatedMarkdownCount;
recommendation.TotalRevenue = totalRevenue;
recommendation.TotalCost = totalCost;
recommendation.TotalMarkdownCost = totalMarkdownCost;
recommendation.FinalDiscount = projections.Any() ? projections.Last().Discount : 0;
recommendation.StockValue = product.CurrentSellingPrice * product.CurrentStock;
recommendation.EstimatedProfit = totalRevenue - totalCost;
recommendation.EstimatedSales = accumulatedStockChange;
recommendation.TerminalStock = terminalStock;
recommendation.SellThroughRate = sellThroughRate;
recommendation.SellThroughTarget = sellThroughTarget;
recommendation.FinalMarkdownType = currentMarkdownType;
recommendation.Projections = projections;
return(true);
}
public void ExpandProjectionWeeks(ref SmCalcProduct product, ref List <SmCalcRecommendation> recommendations, SmScenario scenario, List <decimal> revisedDiscounts)
{
// Calculate week period
var scheduleMask = scenario.ScheduleMask;
var firstWeek = scenario.ScheduleWeekMin;
var lastWeek = scenario.ScheduleWeekMax;
var weekCount = lastWeek - firstWeek + 1;
// Skip if we already have n weeks
var sparseRecommendations = recommendations
.Where(x => x.Projections.Count < weekCount)
.ToList();
if (!sparseRecommendations.Any())
{
return;
}
// Create a week map
var markdownIndex = 0;
var weeks = Enumerable.Range(firstWeek, weekCount)
.Select((x, i) =>
{
var bit = x - firstWeek;
var isMarkdown = ((1 << bit) & scheduleMask) != 0;
return(new
{
Week = x,
WeekIndex = i,
IsMarkdown = isMarkdown,
MarkdownIndex = isMarkdown ? markdownIndex++ : (int?)null
});
})
.ToList();
// Foreach sparse recommendation append missing weeks
foreach (var recommendation in sparseRecommendations)
{
var projections = recommendation.Projections;
var originalSellingPrice = product.OriginalSellingPrice;
var currentMarkdownDepth = product.CurrentMarkdownDepth;
var trailingWeeks = weeks
.Skip(projections.Count)
.ToList();
var lastProjection = projections.LastOrDefault();
var trailingProjections = new List <SmCalcRecommendationProjection>();
var lastDiscount = projections.Any() ? lastProjection.Discount : currentMarkdownDepth;
var previousDiscount = lastDiscount;
var previousPrice = originalSellingPrice * (1 - previousDiscount);
foreach (var item in trailingWeeks)
{
var discount = lastDiscount;
var markdownCount = projections.Any() ? lastProjection.MarkdownCount : 0;
var accumulatedMarkdownCount = projections.Any() ? lastProjection.AccumulatedMarkdownCount : 0;
var stock = projections.Any() ? lastProjection.Stock : 0;
if (revisedDiscounts != null && item.MarkdownIndex != null && item.IsMarkdown && item.MarkdownIndex <= revisedDiscounts.Count - 1)
{
discount = revisedDiscounts[(int)item.MarkdownIndex];
}
var price = originalSellingPrice * (1 - discount);
var projection = new SmCalcRecommendationProjection
{
Week = item.Week,
Discount = discount,
Price = price,
MarkdownCount = markdownCount,
AccumulatedMarkdownCount = accumulatedMarkdownCount,
Quantity = 0,
Revenue = 0,
MarkdownCost = (price - previousPrice) * stock,
Stock = stock,
Decay = 0,
Elasticity = 0
};
previousDiscount = discount;
previousPrice = originalSellingPrice * (1 - previousDiscount);
trailingProjections.Add(projection);
lastProjection = projection;
}
projections.AddRange(trailingProjections);
}
}
public async Task <Tuple <SmScenario, List <SmProduct> > > GetScenarioData(IMarkdownFunctionSettings settings, int partitionId, IEnumerable <int> specificProductIds = null)
{
var isOptional = partitionId > 1;
// Scenario
var scenarioPath = SmS3Path.ScenarioPath(SmS3PathName.Scenario, settings);
var scenario = await _s3Repository.ReadRecord <Scenario>(scenarioPath);
var sellThroughPath = SmS3Path.ScenarioPath(SmS3PathName.HierarchySellThrough, settings);
var sellThrough = await _s3Repository.ReadRecords <HierarchySellThrough>(sellThroughPath);
if (!sellThrough.Any())
{
_logger.Warning("No sell through targets in partition");
}
var priceLadderValuePath = SmS3Path.ScenarioPath(SmS3PathName.PriceLadderValue, settings);
var priceLadderValue = await _s3Repository.ReadRecords <PriceLadderValue>(priceLadderValuePath);
if (!priceLadderValue.Any())
{
_logger.Error("No price ladder values in partition");
}
// Product
var productPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.Product, settings);
var product = await _s3Repository.ReadRecords <Product>(productPath, isOptional);
if (specificProductIds != null)
{
product = product.Where(x => specificProductIds.Contains(x.ProductId)).ToList();
}
if (!product.Any())
{
_logger.Error("No products in partition");
}
var productHierarchyPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductHierarchy, settings);
var productHierarchy = await _s3Repository.ReadRecords <ProductHierarchy>(productHierarchyPath, isOptional);
if (!productHierarchy.Any())
{
_logger.Error("No product hierarchies in partition");
}
var productPriceLadderPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductPriceLadder, settings);
var productPriceLadder = await _s3Repository.ReadRecords <ProductPriceLadder>(productPriceLadderPath, isOptional);
if (!productPriceLadder.Any())
{
_logger.Error("No product price ladders in partition");
}
var productSalesTaxPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductSalesTax, settings);
var productSalesTax = await _s3Repository.ReadRecords <ProductSalesTax>(productSalesTaxPath, isOptional);
if (!productSalesTax.Any())
{
_logger.Warning("No product sales tax in partition");
}
var productParameterValuesPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductParameterValues, settings);
var productParameterValues = await _s3Repository.ReadRecords <ProductParameterValues>(productParameterValuesPath, isOptional);
if (!productParameterValues.Any())
{
_logger.Warning("No product parameter values in partition");
}
var productMarkdownConstraintPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductMarkdownConstraint, settings);
var productMarkdownConstraint = await _s3Repository.ReadRecords <ProductMarkdownConstraint>(productMarkdownConstraintPath, true);
if (!productMarkdownConstraint.Any())
{
_logger.Warning("No product markdown constraint values in partition");
}
var salesFlexFactorPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.SalesFlexFactor, settings);
var salesFlexFactor = await _s3Repository.ReadRecords <ProductSalesFlexFactor>(salesFlexFactorPath, isOptional);
if (!salesFlexFactor.Any())
{
_logger.Warning("No sales flex factor values in partition");
}
var productMinimumAbsolutePriceChangePath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductMinimumAbsolutePriceChange, settings);
var productMinimumAbsolutePriceChange = await _s3Repository.ReadRecords <ProductMinimumAbsolutePriceChange>(productMinimumAbsolutePriceChangePath, true);
if (!productMinimumAbsolutePriceChange.Any())
{
_logger.Warning("No product minimum absolute price change values in partition");
}
var productWeekParameterValuesPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductWeekParameterValues, settings);
var productWeekParameterValues = await _s3Repository.ReadRecords <ProductWeekParameterValues>(productWeekParameterValuesPath, true);
if (!productWeekParameterValues.Any())
{
_logger.Warning("No product week parameter values in partition");
}
var productWeekMarkdownTypeParameterValuesPath = SmS3Path.ScenarioPartitionPath(SmS3PathName.ProductWeekMarkdownTypeParameterValues, settings);
var productWeekMarkdownTypeParameterValues = await _s3Repository.ReadRecords <ProductWeekMarkdownTypeParameterValues>(productWeekMarkdownTypeParameterValuesPath, true);
if (!productWeekMarkdownTypeParameterValues.Any())
{
_logger.Warning("No product week parameter values in partition");
}
// Sell through lookup
var sellThroughLookup = sellThrough.Any() ? sellThrough.ToDictionary(x => x.HierarchyId, x => x.Value) : new Dictionary <int, decimal>();
// Sales tax lookup
var productSalesTaxLookup =
productSalesTax.Any()
? productSalesTax.ToDictionary(x => x.ProductId, x => Tuple.Create(x.Week, x.Rate))
: new Dictionary <int, Tuple <int, decimal> >();
var products = SmProduct.Build(_logger, product, productHierarchy, productPriceLadder, priceLadderValue,
sellThroughLookup, productSalesTaxLookup, productParameterValues, productMarkdownConstraint, salesFlexFactor, productMinimumAbsolutePriceChange, productWeekParameterValues, productWeekMarkdownTypeParameterValues);
return(Tuple.Create(SmScenario.Build(scenario), products));
}