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 Columns = InABox.Core.Columns;

namespace Comal.Stores;

internal class PurchaseOrderItemStore : BaseStore<PurchaseOrderItem>
{

    static PurchaseOrderItemStore()
    {
        RegisterListener<ProductDimensionUnit>(ReloadProductDimensionUnitCache);
    }
    
    private static Dictionary<Guid, ScriptDocument>? _productdimensionunitcache = null;
    
    private static void ReloadProductDimensionUnitCache(Guid[]? ids)
    {
        if (_productdimensionunitcache == null)
            _productdimensionunitcache = new Dictionary<Guid, ScriptDocument>();
        
        var scripts = DbFactory.NewProvider(Logger.Main).Query(
            ids != null
                ? new Filter<ProductDimensionUnit>(x => x.ID).InList(ids)
                : null,
            Columns.None<ProductDimensionUnit>()
                .Add(x => x.ID)
                .Add(x => x.Conversion)
        ).ToDictionary<ProductDimensionUnit, Guid, String>(x => x.ID, x => x.Conversion);
        
        foreach (var id in scripts.Keys)
        {
                var doc = !String.IsNullOrWhiteSpace(scripts[id]) ? new ScriptDocument(scripts[id]) : null;
                if (doc?.Compile() == true)
                    _productdimensionunitcache[id] = doc;
                else
                    _productdimensionunitcache.Remove(id);
        }
    }
    
    private void TransformDimensions(PurchaseOrderItem item)
    {
        if (_productdimensionunitcache == null)
            ReloadProductDimensionUnitCache(null);
        if (_productdimensionunitcache?.TryGetValue(item.Dimensions.Unit.ID, out ScriptDocument? script) == true)
            script.Execute("Module",DimensionUnit.ConvertDimensionsMethodName(), [item]);
    }

    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.JobRequisitionItem.ID)
                    .Add(x => x.JobRequisitionItem.Cancelled))
            .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 instancetask = Task.Run(() =>
        {
            return 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();
        });
        
        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, instancetask, poiaTask, consigntask);

        var instancerow = instancetask.Result;
        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);
        }
        
        TransformDimensions(entity);

        // Actual logic begins here.

        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;
        }        
        
        foreach (var poia in allocations)
        {
            var jri = poia.JobRequisitionItem.ID == Guid.Empty ? null : poia.JobRequisitionItem;
            CreateMovement(entity, locationid, movements, poia.Job, jri, poia.Quantity, poCost);

            //CreateMovement(entity, locationid, movements, poia.Job, poia.JobRequisitionItem, poia.Quantity, poCost);

            //// Going through each jri, make sure we don't allocate more than the po line allows
            //var jriQty = Math.Min(jri.Qty, _pototal);
            //// And reduce the po balance by the jri Allocation
            //_pototal -= jriQty;
            //
            //// Let's not make zero-quantity transactions
            //if (!jriQty.IsEffectivelyEqual(0.0))
            //    CreateMovement(entity, locationid, movements, jri, jriQty, poCost);
        }

        var totalAllocations = allocations.Sum(x => x.Quantity);
        var freeQty = entity.Qty - totalAllocations;
        
        // If there is any left over (ie over/under-ordered), now we can create
        // a second transaction to receive the unallocated stock
        CreateMovement(entity, locationid, movements, null, null, freeQty, poCost);

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

        // Update the AverageCost on the instance
        if (!freeQty.IsEffectivelyEqual(0.0))
        {
            var instance = instancerow?.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 CreateMovement(PurchaseOrderItem entity, Guid locationid, List<StockMovement> movements, IJob? job, IJobRequisitionItem? jri, double qty, double cost)
    {
        if (qty.IsEffectivelyEqual(0.0)) return;

        var movement = new StockMovement();
        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;
        movements.Add(movement);
        
        if (jri is not null)
        {
            movement.JobRequisitionItem.ID = jri.ID;
            if (!jri.Cancelled.IsEmpty())
            {
                // We need to create an immediate transfer in and out of the job requisition item.

                var tOut = movement.CreateMovement();
                tOut.JobRequisitionItem.ID = jri.ID;
                tOut.Date = 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.Type = StockMovementType.TransferOut;

                var tIn = movement.CreateMovement();
                tIn.Transaction = tOut.Transaction;
                tIn.Date = entity.ReceivedDate;
                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 void DeleteStockMovements(PurchaseOrderItem entity)
    {
        var movements = Provider.Query(
            new Filter<StockMovement>(x => x.OrderItem.ID).IsEqualTo(entity.ID),
            Columns.None<StockMovement>().Add(x => x.ID)
        ).Rows.Select(x => x.ToObject<StockMovement>());

        if (movements.Any())
            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);
    }


}