prs/prs.stores/PurchaseOrderItemStore.cs

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Comal.Classes;
using InABox.Core;
using PRSStores;
using System;
using InABox.Database;
using InABox.Scripting;
using NPOI.SS.Formula.Functions;
using PRSDimensionUtils;
using Columns = InABox.Core.Columns;

namespace Comal.Stores;

internal class PurchaseOrderItemStore : BaseStore<PurchaseOrderItem>
{

    static PurchaseOrderItemStore()
    {
        RegisterListener<ProductDimensionUnit>(ReloadDimensionScripts);
    }

    private static void ReloadDimensionScripts(Guid[] ids)
    {
        DimensionUtils.ReloadDimensionScriptCache(
            ids,
            (f, c) => DbFactory.NewProvider(InABox.Core.Logger.Main).Query(f, c)
        );
    }

    // private void TransformDimensions(PurchaseOrderItem item)
    // {
    //     if (_productdimensionunitcache == null)
    //         ReloadProductDimensionUnitCache(null);
    //     if (_productdimensionunitcache?.TryGetValue(item.Dimensions.Unit.ID, out ScriptDocument? script) == true)
    //     {
    //         var oldqty = item.Qty; // 1
    //         if (!oldqty.IsEffectivelyEqual(0.0))
    //         {
    //             script.SetValue("Quantity", oldqty);
    //             script.SetValue("Dimensions", item.Dimensions); // Length = 5.8
    //             script.Execute("Module", DimensionUnit.ConvertDimensionsMethodName()); // Length = 1, Qty = 5.8, 
    //             var newqty = Convert.ToDouble(script.GetValue("Quantity")); // 5.8
    //             item.Qty = newqty;
    //             item.Cost = item.Cost * newqty / oldqty;
    //         }
    //     }
    // }

    private void UpdateStockMovements(PurchaseOrderItem entity)
    {
        var movements = Provider.Query<StockMovement>(
            new Filter<StockMovement>(x => x.OrderItem.ID).IsEqualTo(entity.ID))
            .ToArray<StockMovement>();
        foreach(var mvt in movements)
        {
            mvt.Date = entity.ReceivedDate;
            mvt.Cost = entity.Cost;
        }
        FindSubStore<StockMovement>().Save(movements, "Updated by purchase order modification");
    }
    
    private void CreateStockMovements(PurchaseOrderItem entity)
    {
        if (!entity.Product.IsValid())
        {
            Logger.Send(LogType.Information, UserID, "PurchaseOrderItem.Product.ID is blank!");
            return;
        }
        
        if (entity.Qty == 0)
        {
            Logger.Send(LogType.Information, UserID, "PurchaseOrderItem Qty is blank!");
            return;
        }

        var locationid = entity.StockLocation.ID;
        var locationValid = entity.StockLocation.IsValid();

        var poiaTask = Task.Run(() =>
        {
            return Provider.Query(
                new Filter<PurchaseOrderItemAllocation>(x => x.Item.ID).IsEqualTo(entity.ID),
                Columns.None<PurchaseOrderItemAllocation>()
                    .Add(x => x.ID)
                    .Add(x => x.Job.ID)
                    .Add(x => x.JobRequisitionItem.ID)
                    .Add(x => x.JobRequisitionItem.Cancelled)
                    .Add(x => x.Quantity))
            .ToArray<PurchaseOrderItemAllocation>();
        });

        var consigntask = Task.Run(() =>
        {
            if (entity.Consignment.ID != Guid.Empty && !entity.Consignment.ExTax.IsEffectivelyEqual(0.0))
            {
                var values = Provider.Query(
                    new Filter<PurchaseOrderItem>(x => x.Consignment.ID).IsEqualTo(entity.Consignment.ID),
                    Columns.None<PurchaseOrderItem>().Add(x => x.ExTax)
                ).Rows.Select(r => r.Get<PurchaseOrderItem, double>(c => c.ExTax));
                return values.Sum();
            }
            else
            {
                return 0.0;
            }
        });
        
        var producttask = Task.Run(
            () => Provider.Query(
                new Filter<Product>(x => x.ID).IsEqualTo(entity.Product.ID),
                Columns.None<Product>()
                    .Add(x => x.ID)
                    .Add(x => x.DefaultLocation.ID)
                    .Add(x => x.Warehouse.ID)
                    .AddDimensionsColumns(x => x.DefaultInstance.Dimensions, Dimensions.ColumnsType.All)
                    .Add(x => x.NonStock))
            .Rows.FirstOrDefault());

        var locationtask = Task.Run(
            () => Provider.Query(
                new Filter<StockLocation>(x => x.Default).IsEqualTo(true),
                Columns.None<StockLocation>().Add(x => x.ID, x => x.Warehouse.ID, x => x.Warehouse.Default)));

        Task.WaitAll(producttask, locationtask, poiaTask, consigntask);
        
        var productrow = producttask.Result;
        var defaultlocations = locationtask.Result;
        var allocations = poiaTask.Result.ToArray();
        
        if (productrow is null)
        {
            Logger.Send(LogType.Information, UserID, "Cannot Find PurchaseOrderItem.Product.ID!");
            return;
        }
        if (productrow.Get<Product, bool>(x => x.NonStock))
        {
            Logger.Send(LogType.Information, UserID, "PurchaseOrderItem.Product is marked as Non Stock!");
            return;
        }
        
        if (!locationValid)
        {
            Logger.Send(LogType.Information, UserID, "PurchaseOrderItem.Location.ID is blank!");

            var productlocationid = productrow.EntityLinkID<Product, StockLocationLink>(x => x.DefaultLocation) ?? Guid.Empty;
            if (productlocationid != Guid.Empty)
            {
                Logger.Send(LogType.Information, UserID, "- Using Product.DefaultLocation.ID as location");
                locationid = productlocationid;
            }
            else
            {
                var productwarehouseid = productrow.Get<Product, Guid>(c => c.Warehouse.ID);
                var row = defaultlocations.Rows.FirstOrDefault(r => r.Get<StockLocation, Guid>(c => c.Warehouse.ID) == productwarehouseid);
                if (row != null)
                {
                    Logger.Send(LogType.Information, UserID, "- Using Product.Warehouse -> Default as location");
                    locationid = row.Get<StockLocation, Guid>(x => x.ID);
                }
            }

            if (locationid == Guid.Empty)
            {
                var row = defaultlocations.Rows.FirstOrDefault(r => r.Get<StockLocation, bool>(c => c.Warehouse.Default));
                if (row != null)
                {
                    Logger.Send(LogType.Information, UserID, "- Using Default Warehouse -> Default Location as location");
                    locationid = row.Get<StockLocation, Guid>(x => x.ID);
                }
            }

            if (locationid == Guid.Empty)
            {
                Logger.Send(LogType.Information, UserID, "- Cannot find Location : Skipping Movement Creation");
                return;
            }
        }
        
        if (entity.Dimensions.Unit.ID == Guid.Empty
            && entity.Dimensions.Height == 0
            && entity.Dimensions.Width == 0
            && entity.Dimensions.Length == 0
            && entity.Dimensions.Weight == 0)
        {
            Logger.Send(LogType.Information, UserID, "PurchaseOrderItem.Unit Size is zero!");
            entity.Dimensions.CopyFrom(productrow.ToObject<Product>().DefaultInstance.Dimensions);
        }

        entity.ConvertDimensions(DbFactory.NewProvider(Logger.Main).QueryProvider<ProductDimensionUnit>());
        // Actual logic begins here.
        
        var totalAllocations = allocations.Sum(x => x.Quantity);
        var freeQty = entity.Qty - totalAllocations;

        var instancetask = Task.Run(
            () => !freeQty.IsEffectivelyEqual(0.0) 
                ? Provider.Query(
                    new Filter<ProductInstance>(x => x.Product.ID).IsEqualTo(entity.Product.ID)
                        .And(x => x.Style.ID).IsEqualTo(entity.Style.ID)
                        .And(x => x.Dimensions).DimensionEquals(entity.Dimensions),
                    Columns.Required<ProductInstance>()
                        .Add(x => x.ID)
                        .Add(x => x.FreeStock)
                        .Add(x => x.AverageCost)
                        .Add(x => x.LastCost)
                ).Rows.FirstOrDefault()
                : null
        );
        
        var batch = new StockMovementBatch
        {
            Type = StockMovementBatchType.Receipt,
            TimeStamp = DateTime.Now,
            Notes = $"Received on PO"
        };
        var movements = new List<StockMovement>();

        var _pototal = entity.Qty;
        var poCost = entity.Cost;
        if (!consigntask.Result.IsEffectivelyEqual(0.0)
            && !entity.Qty.IsEffectivelyEqual(0.0))
        { 
            poCost += entity.Cost * entity.Consignment.ExTax / consigntask.Result;
        }

        var transactionID = Guid.NewGuid();

        // First, we receive all stock into the main allocation.
        CreateReceive(movements, entity, locationid, entity.Job, null, entity.Qty, poCost, transactionID);
        
        foreach (var poia in allocations)
        {
            if (poia.Quantity == 0) continue;

            // Then, we make transfers into each of the allocations.
            var jri = poia.JobRequisitionItem.ID == Guid.Empty ? null : poia.JobRequisitionItem;

            var tOut = CreateStockMovement(movements, entity, locationid, entity.Job, null, poCost, transactionID);
            tOut.Type = StockMovementType.TransferOut;
            tOut.Issued = poia.Quantity;
            tOut.System = true;

            var tIn = CreateStockMovement(movements, entity, locationid, poia.Job, jri, poCost, transactionID);
            tIn.Type = StockMovementType.TransferIn;
            tIn.Received = poia.Quantity;
            tIn.Transaction = tOut.Transaction;
            tIn.System = true;

            if(jri is not null && !jri.Cancelled.IsEmpty())
            {
                CreateJRICancelledTransfer(tIn, entity, movements, jri, poia.Quantity, Guid.NewGuid());
            }
        }

        FindSubStore<StockMovementBatch>().Save(batch, "Received on PO");
        foreach(var mvt in movements)
        {
            mvt.Batch.ID = batch.ID;
        }

        // We need the instancetask to return before updating any stock movements
        instancetask.Wait();
        
        FindSubStore<StockMovement>().Save(movements, "Updated by Purchase Order Modification");

        // Update the AverageCost on the instance
        if (!freeQty.IsEffectivelyEqual(0.0))
        {
            var instance = instancetask.Result?.ToObject<ProductInstance>();
            if (instance == null)
            {
                instance = new ProductInstance();
                instance.Product.ID = entity.Product.ID;
                instance.Style.ID = entity.Style.ID;
                instance.Dimensions.CopyFrom(entity.Dimensions);
            }
            
            instance.LastCost = entity.Cost;

            var freeqty = instance.FreeStock;
            var freeavg = instance.AverageCost;
            var freecost = instance.FreeStock * freeavg;

            var poqty = freeQty * (Math.Abs(entity.Dimensions.Value) > 0.0001F ? entity.Dimensions.Value : 1.0F);
            var pocost = entity.Cost * poqty;
            
            if (!consigntask.Result.IsEffectivelyEqual(0.0))
                pocost += freeQty * entity.Cost * entity.Consignment.ExTax / consigntask.Result;
            
            var totalqty = freeqty + poqty;
            var totalcost = freecost + pocost;

            var averagecost = Math.Abs(totalqty) > 0.0001F
                ? totalcost / totalqty
                : pocost;

            if (Math.Abs(averagecost - freeavg) > 0.0001F)
            {
                instance.AverageCost = averagecost;
                FindSubStore<ProductInstance>().Save(instance,
                    $"Updated Average Cost: " +
                    $"({freeqty} @ {freeavg:C2}) + ({poqty} @ {entity.Cost:C2}) = {totalcost:C2} / {totalqty}"
                );
            }
        }

        entity.CancelChanges();
    }

    private static void CreateJRICancelledTransfer(
        IStockHolding movement,
        PurchaseOrderItem entity,
        List<StockMovement> movements,
        IJobRequisitionItem jri,
        double qty,
        Guid transactionID)
    {
        var lastMovement = movements.Count > 0 ? movements[^1] : null;

        var tOut = movement.CreateMovement();
        tOut.JobRequisitionItem.ID = jri.ID;
        tOut.Date = lastMovement is not null ? lastMovement.Date.AddTicks(1) : entity.ReceivedDate;
        tOut.Issued = qty;
        tOut.OrderItem.ID = entity.ID;
        tOut.Notes = "Internal transfer from cancelled requisition";
        tOut.System = true;
        tOut.Cost = entity.Cost;
        tOut.Transaction = transactionID;
        tOut.Type = StockMovementType.TransferOut;

        var tIn = movement.CreateMovement();
        tIn.Transaction = transactionID;
        tIn.Date = tOut.Date.AddTicks(1);
        tIn.Received = qty;
        tIn.OrderItem.ID = entity.ID;
        tOut.Notes = "Internal transfer from cancelled requisition";
        tOut.System = true;
        tIn.Cost = entity.Cost;
        tIn.Type = StockMovementType.TransferIn;

        movements.Add(tOut);
        movements.Add(tIn);
    }

    private static StockMovement CreateStockMovement(
        List<StockMovement> movements,
        PurchaseOrderItem entity,
        Guid locationID,
        IJob? job, IJobRequisitionItem? jri,
        double cost, Guid transactionID)
    {
        var movement = new StockMovement();
        movement.Product.ID = entity.Product.ID;
        movement.Location.ID = locationID;
        movement.Style.ID = entity.Style.ID;
        if(job is not null)
        {
            movement.Job.ID = job.ID;
        }
        movement.Dimensions.CopyFrom(entity.Dimensions);

        var lastMovement = movements.Count > 0 ? movements[^1] : null;

        movement.Date = lastMovement is not null ? lastMovement.Date.AddTicks(1) : entity.ReceivedDate;
        movement.Employee.ID = Guid.Empty;
        movement.Cost = cost;

        movement.Transaction = transactionID;

        if (jri is not null)
        {
            movement.JobRequisitionItem.ID = jri.ID;
        }

        movements.Add(movement);

        return movement;
    }

    private static void CreateReceive(List<StockMovement> movements, PurchaseOrderItem entity, Guid locationid, IJob? job, IJobRequisitionItem? jri, double qty, double cost, Guid transactionID)
    {
        if (qty.IsEffectivelyEqual(0.0)) return;

        var movement = new StockMovement();
        movement.Transaction = transactionID;
        movement.Product.ID = entity.Product.ID;
        if(job is not null)
        {
            movement.Job.ID = job.ID;
        }
        movement.Location.ID = locationid;
        movement.Style.ID = entity.Style.ID;
        movement.Dimensions.CopyFrom(entity.Dimensions);

        movement.Date = entity.ReceivedDate;
        movement.Received = qty;
        movement.Employee.ID = Guid.Empty;
        movement.OrderItem.ID = entity.ID;
        movement.Notes = string.Format("Received on PO {0}", entity.PurchaseOrderLink.PONumber);
        movement.Cost = cost;
        movement.Type = StockMovementType.Receive;
        
        if (jri is not null)
        {
            movement.JobRequisitionItem.ID = jri.ID;
        }

        movements.Add(movement);
    }
    
    private void DeleteStockMovements(PurchaseOrderItem entity)
    {
        var movements = Provider.Query(
            new Filter<StockMovement>(x => x.Transaction)
                .InQuery(new Filter<StockMovement>(x => x.OrderItem.ID).IsEqualTo(entity.ID), x => x.Transaction),
            Columns.None<StockMovement>().Add(x => x.ID)
        ).ToArray<StockMovement>();

        if (movements.Length > 0)
            FindSubStore<StockMovement>().Delete(movements, "Purchase Order Item marked as Unreceived");
    }
    

    protected override void AfterSave(PurchaseOrderItem entity)
    {
        base.AfterSave(entity);
        
        if (entity.HasOriginalValue(x=>x.ReceivedDate))
        {
            if (entity.ReceivedDate.IsEmpty())
                DeleteStockMovements(entity);
            else
            {
                var original = entity.GetOriginalValue(x => x.ReceivedDate);
                if(original == DateTime.MinValue)
                {
                    var item = Provider.Query(
                            new Filter<PurchaseOrderItem>(x => x.ID).IsEqualTo(entity.ID),
                            LookupFactory.RequiredColumns<PurchaseOrderItem>())
                        .ToObjects<PurchaseOrderItem>().FirstOrDefault();
                    if(item is not null)
                        CreateStockMovements(item);
                }
                else
                {
                    var item = Provider.Query(
                            new Filter<PurchaseOrderItem>(x => x.ID).IsEqualTo(entity.ID),
                            Columns.None<PurchaseOrderItem>().Add(x => x.ID)
                                .Add(x => x.ReceivedDate)
                                .Add(x => x.Cost))
                        .ToObjects<PurchaseOrderItem>().FirstOrDefault();
                    if(item is not null)
                        UpdateStockMovements(item);
                    
                }
            }
        }
    }

    private Guid GetJobRequisitionID(PurchaseOrderItem entity)
    {
        var jri = Provider.Query(
                new Filter<PurchaseOrderItemAllocation>(x => x.Item.ID).IsEqualTo(entity.ID),
                Columns.Required<PurchaseOrderItemAllocation>())
            .Rows
            .FirstOrDefault()?
            .Get<PurchaseOrderItemAllocation, Guid>(x => x.JobRequisitionItem.ID) ?? Guid.Empty;
        return jri;
    }
    
    protected override void BeforeDelete(PurchaseOrderItem entity)
    {
        base.BeforeDelete(entity);
        DeleteStockMovements(entity);
    }


}