prs/prs.stores/PurchaseOrderItemStore.cs

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

namespace Comal.Stores
{
    internal class PurchaseOrderItemStore : BaseStore<PurchaseOrderItem>
    {

        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 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
                    )
                ).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);

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

            foreach (var movement in movements)
            {
                movement.Batch.Type = StockMovementBatchType.Receipt;
                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.Style.Code = entity.Style.Code;
                movement.Style.Description = entity.Style.Description;
                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;

            }

            var updates = movements.Where(x => x.IsChanged());
            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);
                else
                {
                    var item = Provider
                        .Query(new Filter<PurchaseOrderItem>(x => x.ID).IsEqualTo(entity.ID))
                        .Rows.FirstOrDefault()?.ToObject<PurchaseOrderItem>();
                    if (item != null)
                    {
                        UpdateStockMovements(item);
                        UpdateJobRequiItems(item);
                    }
                }
            }
        }

        private void UpdateJobRequiItems(PurchaseOrderItem entity)
        {
            var table = Provider.Query(
                new Filter<JobRequisitionItem>(x => x.PurchaseOrderItem.ID).IsEqualTo(entity.ID),
                new Columns<JobRequisitionItem>(x => x.ID, x => x.Status)
                );
            if (table.Rows.Any())
            {
                JobRequisitionItem item = table.Rows.FirstOrDefault().ToObject<JobRequisitionItem>();
                if (item.Status == JobRequisitionItemStatus.TreatmentOnOrder)
                    item.Status = JobRequisitionItemStatus.TreatmentReceived;
                else
                    item.Status = JobRequisitionItemStatus.Received;

                Provider.Save(item);
            }
        }

        protected override void BeforeDelete(PurchaseOrderItem entity)
        {
            base.BeforeDelete(entity);
            DeleteStockMovements(entity);
        }

        protected override void AfterDelete(PurchaseOrderItem entity)
        {
            base.AfterDelete(entity);
            RemoveJobRequisitionItemLink(entity);
        }

        private void RemoveJobRequisitionItemLink(PurchaseOrderItem entity)
        {
            CoreTable table = Provider.Query<JobRequisitionItem>
                (
                new Filter<JobRequisitionItem>(x => x.PurchaseOrderItem.ID).IsEqualTo(entity.ID),
                new Columns<JobRequisitionItem>(x => x.ID, x => x.PurchaseOrderItem.PurchaseOrderLink.PONumber, x => x.Status)
                );
            if (table.Rows.Any())
            {
                JobRequisitionItem item = table.Rows.FirstOrDefault().ToObject<JobRequisitionItem>();
                item.PurchaseOrderItem.PurchaseOrderLink.PONumber = "";
                item.Status = JobRequisitionItemStatus.NotChecked;
                Provider.Save<JobRequisitionItem>(item);
            }
        }
    }
}