prs/prs.stores/StockHoldingStore.cs

using Comal.Classes;
using InABox.Core;
using InABox.Database;
using System.Linq;
using System;
using MathNet.Numerics;

namespace Comal.Stores;

using HoldingDictionary = Dictionary<(Guid product, Guid style, Guid location, Guid job, StockDimensions dimensions), StockHolding>;

public class StockHoldingStore : BaseStore<StockHolding>
{

    public enum Action
    {
        Increase,
        Decrease
    }

    public static StockMovement[] LoadMovementData(IStore store, Guid[] ids)
    {
        return store.Provider.Query(
            new Filter<StockMovement>(x => x.ID).InList(ids),
            Columns.None<StockMovement>().Add(x => x.ID)
                .Add(x => x.Location.ID)
                .Add(x => x.Product.ID)
                .Add(x => x.Style.ID)
                .Add(x => x.Job.ID)
                .Add(x => x.Dimensions.Unit.ID)
                .Add(x => x.Dimensions.Quantity)
                .Add(x => x.Dimensions.Height)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Dimensions.Length)
                .Add(x => x.Dimensions.Weight)
                .Add(x => x.Dimensions.UnitSize)
                .Add(x => x.Dimensions.Value)
                .Add(x => x.JobRequisitionItem.ID)
                .Add(x => x.Units)
                .Add(x => x.Cost)
        ).ToArray<StockMovement>();
    }

    public static HoldingDictionary LoadStockHoldings(IStore store, StockMovement[] mvts, HoldingDictionary? holdings = null)
    {
        if(holdings is not null)
        {
            mvts = mvts.Where(mvt =>
            {
                var key = (mvt.Product.ID, mvt.Style.ID, mvt.Location.ID, mvt.Job.ID, mvt.Dimensions);
                return !holdings.ContainsKey(key);
            }).ToArray();
        }
        else
        {
            holdings = new();
        }

        var productIDs = mvts.Select(x => x.Product.ID).Distinct().ToArray();
        var locationIDs = mvts.Select(x => x.Location.ID).Distinct().ToArray();
        var styleIDs = mvts.Select(x => x.Style.ID).Distinct().ToArray();
        var jobIDs = mvts.Select(x => x.Job.ID).Distinct().ToArray();

        var newHoldings = store.Provider.Query(new Filter<StockHolding>(x => x.Product.ID).InList(productIDs)
                .And(x => x.Location.ID).InList(locationIDs)
                .And(x => x.Style.ID).InList(styleIDs)
                .And(x => x.Job.ID).InList(jobIDs),
                Columns.None<StockHolding>().Add(x => x.ID)
                    .Add(x => x.Units)
                    .Add(x => x.Qty)
                    .Add(x => x.Value)
                    .Add(x => x.Available)
                    .Add(x => x.Weight)
                    .Add(x => x.AverageValue)
                    .Add(x => x.Product.ID)
                    .Add(x => x.Location.ID)
                    .Add(x => x.Style.ID)
                    .Add(x => x.Job.ID)
                    .AddDimensionsColumns(x => x.Dimensions, Dimensions.ColumnsType.Local)
            ).ToObjects<StockHolding>();
        foreach(var holding in newHoldings)
        {
            holdings[(holding.Product.ID, holding.Style.ID, holding.Location.ID, holding.Job.ID, holding.Dimensions)] = holding;
        }

        return holdings;
    }

    public static void ModifyHoldings(StockMovement[] mvts, HoldingDictionary holdings, Action action)
    {
        foreach(var mvt in mvts)
        {
            var key = (mvt.Product.ID, mvt.Style.ID, mvt.Location.ID, mvt.Job.ID, mvt.Dimensions);
            var holding = holdings.GetValueOrDefault(key);
            if(holding is null)
            {
                holding = new();
                holding.Location.ID = mvt.Location.ID;
                holding.Product.ID = mvt.Product.ID;
                holding.Style.ID = mvt.Style.ID;
                holding.Job.ID = mvt.Job.ID;
                holding.Dimensions.CopyFrom(mvt.Dimensions);
                holdings[key] = holding;
            }

            double multiplier = action == Action.Increase ? 1F : -1F;
            holding.Units += (multiplier * mvt.Units);
            holding.Qty += (multiplier * mvt.Units * mvt.Dimensions.Value);
            holding.Value += (multiplier * mvt.Units * mvt.Cost);
            holding.Available += (multiplier * (mvt.JobRequisitionItem.ID == Guid.Empty ? mvt.Units : 0.0));
            
            holding.Weight = holding.Qty * holding.Dimensions.Weight;
            holding.AverageValue = holding.Units != 0 ? holding.Value / holding.Units : 0.0F;
        }
    }

    public static void SaveHoldings(IStore store, HoldingDictionary holdings)
    {
        var holdingStore = store.FindSubStore<StockHolding>();
        holdingStore.Delete(
            holdings.Values.Where(x => x.ID != Guid.Empty && x.Units.IsEffectivelyEqual(0.0) && x.Available.IsEffectivelyEqual(0.0)), "");
        holdingStore.Save(
            holdings.Values.Where(x => x.IsChanged() && (!x.Units.IsEffectivelyEqual(0.0) || !x.Available.IsEffectivelyEqual(0.0))), "");
    }

    public static void UpdateStockHoldings(IStore store, Guid[] ids, Action action)
    {
        var movements = LoadMovementData(store, ids);
        var holdings = LoadStockHoldings(store, movements);
        ModifyHoldings(movements, holdings, action);
        SaveHoldings(store, holdings);
    }

    /// <summary>
    /// Maintains the Stock Holding Table when manipulating Stock Movements
    /// We only accept an ID, because the rest of the movement is pulled from the database
    /// (slower, but more reliable)
    /// </summary>
    /// <param name="id">The id of the Stock Movement to query</param>
    /// <param name="action">The action to perform (increase / decrease)</param>
    public static void UpdateStockHolding(IStore store, Guid id, Action action)
    {
        var movement = store.Provider.Query(
            new Filter<StockMovement>(x => x.ID).IsEqualTo(id),
            Columns.None<StockMovement>().Add(x => x.ID)
                .Add(x => x.Location.ID)
                .Add(x => x.Product.ID)
                .Add(x => x.Style.ID)
                .Add(x => x.Job.ID)
                .Add(x => x.Dimensions.Unit.ID)
                .Add(x => x.Dimensions.Quantity)
                .Add(x => x.Dimensions.Height)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Dimensions.Length)
                .Add(x => x.Dimensions.Weight)
                .Add(x => x.Dimensions.UnitSize)
                .Add(x => x.Dimensions.Value)
                .Add(x => x.JobRequisitionItem.ID)
                .Add(x => x.Units)
                .Add(x => x.Cost)
        ).Rows
            .FirstOrDefault()?
            .ToObject<StockMovement>();
        if (movement == null)
            return;
        
        var holding = store.Provider.Query(new Filter<StockHolding>(x => x.Product.ID).IsEqualTo(movement.Product.ID)
                .And(x => x.Location.ID).IsEqualTo(movement.Location.ID)
                .And(x => x.Style.ID).IsEqualTo(movement.Style.ID)
                .And(x => x.Job.ID).IsEqualTo(movement.Job.ID)
                .And(x => x.Dimensions).DimensionEquals(movement.Dimensions),
                Columns.None<StockHolding>().Add(x => x.ID)
                    .Add(x => x.Units)
                    .Add(x => x.Qty)
                    .Add(x => x.Value)
                    .Add(x => x.Available)
            ).Rows
            .FirstOrDefault()?
            .ToObject<StockHolding>();
        if (holding == null)
        {
            holding = new();
            holding.Location.ID = movement.Location.ID;
            holding.Product.ID = movement.Product.ID;
            holding.Style.ID = movement.Style.ID;
            holding.Job.ID = movement.Job.ID;
            holding.Dimensions.CopyFrom(movement.Dimensions);
        }

        double multiplier = action == Action.Increase ? 1F : -1F;
        holding.Units += (multiplier * movement.Units);
        holding.Qty += (multiplier * movement.Units * movement.Dimensions.Value);
        holding.Value += (multiplier * movement.Units * movement.Cost);
        holding.Available += (multiplier * (movement.JobRequisitionItem.ID == Guid.Empty ? movement.Units : 0.0));
        
        holding.Weight = holding.Qty * holding.Dimensions.Weight;
        holding.AverageValue = holding.Units != 0 ? holding.Value / holding.Units : 0.0F;

        // Automagically clean up empty holdings
        if (holding.Units.IsEffectivelyEqual(0.0) && holding.Available.IsEffectivelyEqual(0.0))
        {
            if (holding.ID != Guid.Empty)
                DbFactory.NewProvider(Logger.Main).Delete(holding, "");
        }
        else
            DbFactory.NewProvider(Logger.Main).Save(holding);
    }
    
}