prs/prs.stores/PurchaseOrderItemStore.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Comal.Classes;
using InABox.Core;
using PRSStores;

namespace Comal.Stores;

internal class PurchaseOrderItemStore : BaseStore<PurchaseOrderItem>
{
    private Columns<PurchaseOrderItem> RequiredColumns()
    {
        return new Columns<PurchaseOrderItem>(x => x.ID)
            .Add(x => x.Product.ID)
            .Add(x => x.Qty)
            .Add(x => x.Cost)
            .Add(x => x.StockLocation.ID)
            .Add(x => x.Style.ID)
            .Add(x => x.Job.ID)
            .Add(x => x.ReceivedDate)
            .Add(x => x.PurchaseOrderLink.PONumber)
            .AddDimensionsColumns(x => x.Dimensions, Dimensions.ColumnsType.Data);
    }

    private void UpdateStockMovements(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 jobRequisitionItemTask = Task<Guid>.Run(() =>
        {
            return Provider.Query(
                new Filter<JobRequisitionItemPurchaseOrderItem>(x => x.PurchaseOrderItem.ID).IsEqualTo(entity.ID),
                new Columns<JobRequisitionItemPurchaseOrderItem>(x => x.JobRequisitionItem.ID))
                .ToObjects<JobRequisitionItemPurchaseOrderItem>().FirstOrDefault()?.JobRequisitionItem.ID ?? Guid.Empty;
        });

        var movementtask = new Task<List<StockMovement>>(() =>
        {
            var result = Provider.Query(
                new Filter<StockMovement>(x => x.OrderItem.ID).IsEqualTo(entity.ID),
                new Columns<StockMovement>(
                    x => x.ID,
                    x => x.Date,
                    x => x.Product.ID,
                    x => x.Received,
                    x => x.Employee.ID,
                    x => x.OrderItem.ID,
                    x => x.Job.ID,
                    x => x.Location.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.Notes,
                    x => x.Cost,
                    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.Batch.ID
                )
            ).Rows.Select(x => x.ToObject<StockMovement>()).ToList();
            if (!result.Any())
                result.Add(new StockMovement());
            return result;
        });
        movementtask.Start();

        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.UnitSize).IsEqualTo(entity.Dimensions.UnitSize),
                new Columns<ProductInstance>(
                    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),
                new Columns<Product>(
                    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),
                new Columns<StockLocation>(x => x.ID, x => x.Warehouse.ID, x => x.Warehouse.Default)
            );
        });
        locationtask.Start();

        Task.WaitAll(movementtask, producttask, locationtask, instancetask, jobRequisitionItemTask);

        var movements = movementtask.Result;
        var instancerow = instancetask.Result;
        var productrow = producttask.Result;
        var defaultlocations = locationtask.Result;
        var jobRequisitionItemID = jobRequisitionItemTask.Result;
        
        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);
        }

        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;

            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}"
                );
            }
        }

        StockMovementBatch? batch = null;
        foreach (var movement in movements)
        {
            if(movement.Batch.ID == Guid.Empty)
            {
                batch ??= new StockMovementBatch
                {
                    Type = StockMovementBatchType.Receipt,
                    TimeStamp = DateTime.Now,
                    Notes = $"Received on PO"
                };
            }

            movement.Date = entity.ReceivedDate;
            movement.Product.ID = entity.Product.ID;
            movement.Received = entity.Qty;
            movement.Employee.ID = Guid.Empty;
            movement.OrderItem.ID = entity.ID;
            movement.Job.ID = entity.Job.ID;
            movement.Location.ID = locationid;
            movement.Style.ID = entity.Style.ID;
            movement.Notes = string.Format("Received on PO {0}", entity.PurchaseOrderLink.PONumber);
            movement.Cost = entity.Cost;
            movement.Type = StockMovementType.Receive;
            movement.Dimensions.Unit.ID = entity.Dimensions.Unit.ID;
            movement.Dimensions.Height = entity.Dimensions.Height;
            movement.Dimensions.Length = entity.Dimensions.Length;
            movement.Dimensions.Width = entity.Dimensions.Width;
            movement.Dimensions.Weight = entity.Dimensions.Weight;
            movement.Dimensions.Quantity = entity.Dimensions.Quantity;
            movement.Dimensions.UnitSize = entity.Dimensions.UnitSize;
            movement.Dimensions.Value = entity.Dimensions.Value;
            movement.Dimensions.UnitSize = entity.Dimensions.UnitSize;
            movement.JobRequisitionItem.ID = jobRequisitionItemID;
        }

        if(batch is not null)
        {
            FindSubStore<StockMovementBatch>().Save(batch, "Received on PO");
            foreach(var movement in movements)
            {
                if(movement.Batch.ID == Guid.Empty)
                {
                    movement.Batch.ID = batch.ID;
                }
            }
        }

        var updates = movements.Where(x => x.IsChanged()).ToList();
        if (updates.Any())
            FindSubStore<StockMovement>().Save(updates, "Updated by Purchase Order Modification");
    }


    private void DeleteStockMovements(PurchaseOrderItem entity)
    {
        var movements = Provider.Query(
            new Filter<StockMovement>(x => x.OrderItem.ID).IsEqualTo(entity.ID),
            new Columns<StockMovement>(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<PurchaseOrderItem,DateTime>(x=>x.ReceivedDate))
        {
            if (entity.ReceivedDate.IsEmpty())
            {
                DeleteStockMovements(entity);
                UpdateJobRequiItems(entity, JobRequisitionItemAction.Updated);
            }
            else
            {
                var item = Provider.Query(
                        new Filter<PurchaseOrderItem>(x => x.ID).IsEqualTo(entity.ID),
                        RequiredColumns())
                    .ToObjects<PurchaseOrderItem>().FirstOrDefault();
                if (item != 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),
                new Columns<JobRequisitionItemPurchaseOrderItem>(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);
    }
}