prs/prs.shared/Database Update Scripts/Update_8_23.cs

using System.Linq.Expressions;
using Comal.Classes;
using InABox.Configuration;
using InABox.Core;
using InABox.Database;
using NPOI.SS.Formula.Functions;
using Columns = InABox.Core.Columns;

namespace PRS.Shared.Database_Update_Scripts;

internal class Update_8_23 : DatabaseUpdateScript
{
    
    public override VersionNumber Version => new(8, 23);
    
    public override bool Update()
    {
        var _provider = DbFactory.NewProvider(Logger.Main);
        CheckDimensions<RequisitionItem>(_provider);
        
        CheckDimensions<StockMovement>(_provider);

        var holdings = _provider.Query(
            null,
            Columns.None<StockHolding>()
                .Add(x => x.Location.ID)
                .Add(x => x.Product.ID)
                .Add(x => x.Job.ID)
                .Add(x => x.Style.ID)
                .Add(x => x.Dimensions.Unit.ID)
                .Add(x => x.Dimensions.Length)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Dimensions.Height)
                .Add(x => x.Dimensions.Quantity)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.AverageValue)
        ).Rows.ToObjects<StockHolding>().ToArray();
        
        var instances = _provider.Query(
            null,
            Columns.None<ProductInstance>()
                .Add(x => x.Product.ID)
                .Add(x => x.Style.ID)
                .Add(x => x.Dimensions.Unit.ID)
                .Add(x => x.Dimensions.Length)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Dimensions.Height)
                .Add(x => x.Dimensions.Quantity)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.AverageCost)
        ).Rows.ToObjects<ProductInstance>().ToArray();

        //CheckStockMovementCosts(_provider, holdings, instances);

        //RecalculateHoldings(_provider, holdings);
        
        return true;
    }

    // private void RecalculateHoldings(IProvider provider, StockHolding[] holdings)
    // {
    //     foreach (var holding in holdings)
    //     {
    //         var movements = provider.Query(
    //             new Filter<StockMovement>(x=>x.Product.ID))
    //     }
    // }

    private void CheckStockMovementCosts(IProvider provider, StockHolding[] holdings, ProductInstance[] instances)
    {
        
        var items = provider.Query(
            new Filter<StockMovement>(x=>x.Product.ID).IsNotEqualTo(Guid.Empty).And(x=>x.Cost).IsEqualTo(0.0),
            Columns.None<StockMovement>()
                .Add(x => x.ID)
                .Add(x => x.Location.ID)
                .Add(x => x.Product.ID)
                .Add(x => x.Job.ID)
                .Add(x => x.Style.ID)
                .Add(x => x.Dimensions.Unit.ID)
                .Add(x => x.Dimensions.Length)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Dimensions.Height)
                .Add(x => x.Dimensions.Quantity)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Cost)
        ).Rows.ToQueue();
        
        while (items.Any())
        {
            Logger.Send(LogType.Information,"",$"Updating {nameof(StockMovement)} Costs ({items.Count} remaining)..");
            var updates = items.Dequeue(100).ToObjects<StockMovement>().ToArray();
            foreach (var item in updates)
            {
                var holding = holdings.FirstOrDefault(x =>
                    Guid.Equals(x.Product.ID, item.Product.ID)
                    && Guid.Equals(x.Location.ID, item.Location.ID)
                    && Guid.Equals(x.Style.ID, item.Style.ID)
                    && Guid.Equals(x.Dimensions.Unit.ID, item.Dimensions.Unit.ID)
                    && x.Dimensions.Length.IsEffectivelyEqual(item.Dimensions.Length)
                    && x.Dimensions.Width.IsEffectivelyEqual(item.Dimensions.Width)
                    && x.Dimensions.Height.IsEffectivelyEqual(item.Dimensions.Height)
                    && x.Dimensions.Weight.IsEffectivelyEqual(item.Dimensions.Weight)
                    && x.Dimensions.Quantity.IsEffectivelyEqual(item.Dimensions.Quantity)
                );
                if (holding != null)
                    item.Cost = holding.AverageValue;
                else
                {
                    var instance = instances.FirstOrDefault(x =>
                        Guid.Equals(x.Product.ID, item.Product.ID)
                        && Guid.Equals(x.Style.ID, item.Style.ID)
                        && Guid.Equals(x.Dimensions.Unit.ID, item.Dimensions.Unit.ID)
                        && x.Dimensions.Length.IsEffectivelyEqual(item.Dimensions.Length)
                        && x.Dimensions.Width.IsEffectivelyEqual(item.Dimensions.Width)
                        && x.Dimensions.Height.IsEffectivelyEqual(item.Dimensions.Height)
                        && x.Dimensions.Weight.IsEffectivelyEqual(item.Dimensions.Weight)
                        && x.Dimensions.Quantity.IsEffectivelyEqual(item.Dimensions.Quantity)
                    );
                    if (instance != null)
                        item.Cost = instance.AverageCost;
                }
            }
            provider.Save(updates.Where(x=>x.IsChanged()));
        }
    }
    
       private void CheckRequisitionItemCosts(IProvider provider, StockHolding[] holdings, ProductInstance[] instances)
    {
        
        var items = provider.Query(
            new Filter<RequisitionItem>(x=>x.Product.ID).IsNotEqualTo(Guid.Empty).And(x=>x.Cost).IsEqualTo(0.0),
            Columns.None<RequisitionItem>()
                .Add(x => x.ID)
                .Add(x => x.Location.ID)
                .Add(x => x.Product.ID)
                .Add(x => x.RequisitionLink.JobLink.ID)
                .Add(x => x.Style.ID)
                .Add(x => x.Dimensions.Unit.ID)
                .Add(x => x.Dimensions.Length)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Dimensions.Height)
                .Add(x => x.Dimensions.Quantity)
                .Add(x => x.Dimensions.Width)
                .Add(x => x.Cost)
        ).Rows.ToObjects<RequisitionItem>().ToQueue();
        
        while (items.Any())
        {
            Logger.Send(LogType.Information,"",$"Updating {nameof(RequisitionItem)} Costs ({items.Count} remaining)..");
            var updates = items.Dequeue(100).ToArray();
            foreach (var item in updates)
            {
                var holding = holdings.FirstOrDefault(x =>
                    Guid.Equals(x.Product.ID, item.Product.ID)
                    && Guid.Equals(x.Location.ID, item.Location.ID)
                    && Guid.Equals(x.Style.ID, item.Style.ID)
                    && Guid.Equals(x.Dimensions.Unit.ID, item.Dimensions.Unit.ID)
                    && x.Dimensions.Length.IsEffectivelyEqual(item.Dimensions.Length)
                    && x.Dimensions.Width.IsEffectivelyEqual(item.Dimensions.Width)
                    && x.Dimensions.Height.IsEffectivelyEqual(item.Dimensions.Height)
                    && x.Dimensions.Weight.IsEffectivelyEqual(item.Dimensions.Weight)
                    && x.Dimensions.Quantity.IsEffectivelyEqual(item.Dimensions.Quantity)
                );
                if (holding != null)
                    item.Cost = holding.AverageValue;
                else
                {
                    var instance = instances.FirstOrDefault(x =>
                        Guid.Equals(x.Product.ID, item.Product.ID)
                        && Guid.Equals(x.Style.ID, item.Style.ID)
                        && Guid.Equals(x.Dimensions.Unit.ID, item.Dimensions.Unit.ID)
                        && x.Dimensions.Length.IsEffectivelyEqual(item.Dimensions.Length)
                        && x.Dimensions.Width.IsEffectivelyEqual(item.Dimensions.Width)
                        && x.Dimensions.Height.IsEffectivelyEqual(item.Dimensions.Height)
                        && x.Dimensions.Weight.IsEffectivelyEqual(item.Dimensions.Weight)
                        && x.Dimensions.Quantity.IsEffectivelyEqual(item.Dimensions.Quantity)
                    );
                    if (instance != null)
                        item.Cost = instance.AverageCost;
                }
            }
            provider.Save(updates.Where(x=>x.IsChanged()));
        }
    }
    
    private void CheckDimensions<T>(IProvider provider) where T : StockEntity, new()
    {
        var items = provider.Query(
            new Filter<T>(x => x.Dimensions.Unit.ID).IsNotEqualTo(Guid.Empty).And(x=>x.Dimensions.Value).IsEqualTo(0.0),
            Columns.Required<T>()
        ).Rows.ToObjects<T>().ToQueue();
        while (items.Any())
        {
            Logger.Send(LogType.Information,"",$"Updating {typeof(T).Name.Split('.').Last()} Dimensions ({items.Count} remaining)..");
            var updates = items.Dequeue(100).ToArray();
            foreach (var item in updates)
                item.Dimensions.CalculateValueAndUnitSize();
            provider.Save(updates);
        }
    }
    
    
}