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 jriTask = Task<Guid>.Run(() =>
        {
            return Provider.Query(
                new Filter<JobRequisitionItem>(x => x.ID).InQuery(
                    new Filter<JobRequisitionItemPurchaseOrderItem>(x => x.PurchaseOrderItem.ID).IsEqualTo(entity.ID),
                    x => x.JobRequisitionItem.ID),
                Columns.None<JobRequisitionItem>().Add(x => x.ID)
                    .Add(x => x.Status)
                    .Add(x=>x.Qty)
                )
            .ToObjects<JobRequisitionItem>();
        });

        var consigntask = Task<double>.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(x => x);
            }

            return 0.0;
        });
        
        var instancetask = new Task<CoreRow?>(() =>
        {
            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,
                    x => x.Product.NonStock,
                    x => x.Product.DefaultLocation.ID,
                    x => x.Product.Warehouse.ID,
                    x => x.Dimensions.Unit.ID,
                    x => x.Dimensions.Unit.Formula,
                    x => x.Dimensions.Unit.Format,
                    x => x.Dimensions.Quantity,
                    x => x.Dimensions.Length,
                    x => x.Dimensions.Width,
                    x => x.Dimensions.Height,
                    x => x.Dimensions.Weight,
                    x => x.Dimensions.Unit.HasHeight,
                    x => x.Dimensions.Unit.HasLength,
                    x => x.Dimensions.Unit.HasWidth,
                    x => x.Dimensions.Unit.HasWeight,
                    x => x.Dimensions.Unit.HasQuantity,
                    x => x.Dimensions.Unit.Formula,
                    x => x.Dimensions.Unit.Format,
                    x => x.Dimensions.Unit.Code,
                    x => x.Dimensions.Unit.Description,
                    
                    x => x.FreeStock,
                    x => x.AverageCost,
                    x => x.LastCost
                )
            ).Rows.FirstOrDefault();
        });
        instancetask.Start();
        
        var producttask = new Task<CoreRow?>(() =>
        {
            return Provider.Query(
                new Filter<Product>(x => x.ID).IsEqualTo(entity.Product.ID),
                Columns.None<Product>().Add(
                    x => x.ID,
                    x => x.DefaultLocation.ID,
                    x => x.Warehouse.ID,
                    x => x.DefaultInstance.Dimensions.Unit.ID,
                    x => x.DefaultInstance.Dimensions.Unit.Formula,
                    x => x.DefaultInstance.Dimensions.Unit.Format,
                    x => x.DefaultInstance.Dimensions.Quantity,
                    x => x.DefaultInstance.Dimensions.Length,
                    x => x.DefaultInstance.Dimensions.Width,
                    x => x.DefaultInstance.Dimensions.Height,
                    x => x.DefaultInstance.Dimensions.Weight,
                    x => x.NonStock,
                    x => x.DefaultInstance.Dimensions.Unit.HasHeight,
                    x => x.DefaultInstance.Dimensions.Unit.HasLength,
                    x => x.DefaultInstance.Dimensions.Unit.HasWidth,
                    x => x.DefaultInstance.Dimensions.Unit.HasWeight,
                    x => x.DefaultInstance.Dimensions.Unit.HasQuantity,
                    x => x.DefaultInstance.Dimensions.Unit.Formula,
                    x => x.DefaultInstance.Dimensions.Unit.Format,
                    x => x.DefaultInstance.Dimensions.Unit.Code,
                    x => x.DefaultInstance.Dimensions.Unit.Description
                )
            ).Rows.FirstOrDefault();
        });
        producttask.Start();

        var locationtask = new Task<CoreTable>(() =>
        {
            return 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)
            );
        });
        locationtask.Start();

        Task.WaitAll(producttask, locationtask, instancetask, jriTask, consigntask);

        var instancerow = instancetask.Result;
        var productrow = producttask.Result;
        var defaultlocations = locationtask.Result;
        var jris = jriTask.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);

        if (entity.Job.ID == Guid.Empty)
        {
            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.Unit.ID = entity.Dimensions.Unit.ID;
                instance.Dimensions.Height = entity.Dimensions.Height;
                instance.Dimensions.Length = entity.Dimensions.Length;
                instance.Dimensions.Width = entity.Dimensions.Width;
                instance.Dimensions.Weight = entity.Dimensions.Weight;
                instance.Dimensions.Quantity = entity.Dimensions.Quantity;
                instance.Dimensions.UnitSize = entity.Dimensions.UnitSize;
                instance.Dimensions.Value = entity.Dimensions.Value;
                instance.Dimensions.UnitSize = entity.Dimensions.UnitSize;
            }

            instance.LastCost = entity.Cost;
                
            //var product = productrow.ToObject<Product>();

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

            var poqty = entity.Qty * (Math.Abs(entity.Dimensions.Value) > 0.0001F ? entity.Dimensions.Value : 1.0F);
            var pocost = entity.Cost * poqty;
            
            if (!consigntask.Result.IsEffectivelyEqual(0.0) && !entity.Qty.IsEffectivelyEqual(0.0))
                pocost += entity.Qty * 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}"
                );
            }
        }

        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 jri in jris)
        {
            // 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);
        }
        
        // If there is any left over (ie over-ordered), now we can create a
        // second transaction to receive the unallocated stock
        if (!_pototal.IsEffectivelyEqual(0.0F))
            CreateMovement(entity, locationid, movements, null, _pototal, 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");
        entity.CancelChanges();
    }

    private static void CreateMovement(PurchaseOrderItem entity, Guid locationid, List<StockMovement> movements, JobRequisitionItem jri, double qty, double cost)
    {
        var movement = new StockMovement();
        movement.Product.ID = entity.Product.ID;
        movement.Job.ID = entity.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 = jri.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 = jri.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);
                UpdateJobRequiItems(entity, JobRequisitionItemAction.Updated);
            }
            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);
                        UpdateJobRequiItems(item, JobRequisitionItemAction.Updated);
                    }
                }
                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);
                        UpdateJobRequiItems(item, JobRequisitionItemAction.Updated);
                    }
                }
            }
        }
        else if(entity.HasOriginalValue(x => x.ID)
            || entity.HasOriginalValue(x => x.Product.ID)
            || entity.HasOriginalValue(x => x.Qty))
        {
            UpdateJobRequiItems(entity, entity.HasOriginalValue(x => x.ID) ? JobRequisitionItemAction.Created : JobRequisitionItemAction.Updated);
        }
    }

    private Guid GetJobRequisitionID(PurchaseOrderItem entity)
    {
        var jri = Provider.Query(
                new Filter<JobRequisitionItemPurchaseOrderItem>(x => x.PurchaseOrderItem.ID).IsEqualTo(entity.ID),
                Columns.None<JobRequisitionItemPurchaseOrderItem>().Add(x => x.JobRequisitionItem.ID))
            .Rows
            .FirstOrDefault()?
            .Get<JobRequisitionItemPurchaseOrderItem, Guid>(x => x.JobRequisitionItem.ID) ?? Guid.Empty;
        return jri;
    }
    
    private void UpdateJobRequiItems(PurchaseOrderItem entity, JobRequisitionItemAction action)
    {
        var id = GetJobRequisitionID(entity);
        if (id != Guid.Empty)
            JobRequisitionItemStore.UpdateStatus(this, id, action);
    }

    private Guid _jobrequisitionitemid = Guid.Empty;
    
    protected override void BeforeDelete(PurchaseOrderItem entity)
    {
        base.BeforeDelete(entity);
        DeleteStockMovements(entity);
        _jobrequisitionitemid = GetJobRequisitionID(entity);
    }

    protected override void AfterDelete(PurchaseOrderItem entity)
    {
        base.AfterDelete(entity);
        if (_jobrequisitionitemid != Guid.Empty)
            JobRequisitionItemStore.UpdateStatus(this, _jobrequisitionitemid, JobRequisitionItemAction.Deleted);
    }
}