prs/prs.classes/Entities/Job/Requisitions/JobRequisitionItem.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Linq.Expressions;
using Comal.Classes.SecurityDescriptors;
using InABox.Clients;
using InABox.Core;
using PRSClasses;

namespace Comal.Classes
{
    public enum JobRequisitionItemStatus
    {
        Stock, 
        Treatment,
        Allocated,
        Issued,
        Cancelled,
        Archived
    }

    public interface IJobRequisitionItem : IEntity
    {
        public DateTime Cancelled { get; set; }
    }

    [Caption("Items")]
    [UserTracking(typeof(Job))]
    public class JobRequisitionItem : StockEntity, IRemotable, IPersistent, IOneToMany<JobRequisition>,
        ILicense<ProjectManagementLicense>, IJobMaterial, ISequenceable, IIssues, IJobRequisitionItem, IProblems<ManagedProblem>
    {
        [EntityRelationship(DeleteAction.Cascade)]
        [Editable(Editable.Hidden)]
        public JobLink Job { get; set; }

        [EntityRelationship(DeleteAction.Cascade)]
        [Editable(Editable.Hidden)]
        public JobRequisitionLink Requisition { get; set; }

        [EntityRelationship(DeleteAction.SetNull)]
        [EditorSequence(1)]
        [RequiredColumn]
        public override ProductLink Product { get; set; }
        
        [EditorSequence(2)]
        [RequiredColumn]
        public ProductStyleLink Style { get; set; }


        [NullEditor]
        [Obsolete("Replaced with Dimensions", true)]
        public double UnitSize { get; set; }

        [EditorSequence(3)]
        [RequiredColumn]
        [DimensionsEditor(typeof(StockDimensions))]
        public override StockDimensions Dimensions { get; set; }
        
        [EditorSequence(4)]
        [RequiredColumn]
        public double Qty { get; set; }        
        
        private class TotalQtyFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                Formula(FormulaOperator.Multiply, Property(x => x.Qty), Property(x => x.Dimensions.Value));
        }
        [DoubleEditor(Editable = Editable.Hidden)]
        [ComplexFormula(typeof(TotalQtyFormula))]
        public double TotalQty { get; set; }
        
        [EditorSequence(5)]
        public double UnitCost { get; set; }

        [EditorSequence(6)]
        [CurrencyEditor(Summary = Summary.Sum)]
        public double TotalCost { get; set; }
        
        [EditorSequence(7)]
        [MemoEditor]
        public string Notes { get; set; }
        
        [EditorSequence(8)]
        public SupplierLink Supplier { get; set; }


        private class StatusFormula : ComplexFormulaGenerator<JobRequisitionItem, JobRequisitionItemStatus>
        {

            public override IComplexFormulaNode<JobRequisitionItem, JobRequisitionItemStatus> GetFormula() =>

                If<JobRequisitionItem, DateTime, JobRequisitionItemStatus>(
                    Property<JobRequisitionItem,DateTime>(x => x.Cancelled),
                    Condition.NotEqual,
                    Constant<JobRequisitionItem,DateTime>(DateTime.MinValue),
                    DateTime.MinValue
                ).Then(
                    Constant(JobRequisitionItemStatus.Cancelled)
                ).Else(
                    If<JobRequisitionItem, DateTime, JobRequisitionItemStatus>(
                        Property<JobRequisitionItem,DateTime>(x => x.Archived),
                        Condition.NotEqual,
                        Constant<JobRequisitionItem,DateTime>(DateTime.MinValue),
                        DateTime.MinValue
                    ).Then(
                        Constant(JobRequisitionItemStatus.Archived)
                    ).Else(
                        If<JobRequisitionItem, double, JobRequisitionItemStatus>(
                            Property<JobRequisitionItem,double>(x => x.Issued),
                            Condition.GreaterThanOrEqualTo,
                            Property<JobRequisitionItem,double>(x => x.Qty)
                        ).Then(
                            Constant(JobRequisitionItemStatus.Issued)
                        ).Else(
                            If<JobRequisitionItem, double, JobRequisitionItemStatus>(
                                Formula(
                                    FormulaOperator.Add,
                                    Property<JobRequisitionItem,double>(x => x.Issued),
                                    Property<JobRequisitionItem,double>(x => x.Allocated)
                                ),
                                Condition.GreaterThanOrEqualTo,
                                Property<JobRequisitionItem,double>(x => x.Qty)
                            ).Then(
                                Constant(JobRequisitionItemStatus.Allocated)
                            ).Else(
                                If<JobRequisitionItem, double, JobRequisitionItemStatus>(
                                    Formula(
                                        FormulaOperator.Add,
                                        Property<JobRequisitionItem,double>(x => x.InStock),
                                        Property<JobRequisitionItem,double>(x => x.Issued)
                                    ),
                                    Condition.GreaterThanOrEqualTo,
                                    Property<JobRequisitionItem,double>(x => x.Qty)
                                ).Then(
                                    Constant(JobRequisitionItemStatus.Treatment)
                                ).Else(
                                    Constant(JobRequisitionItemStatus.Stock)
                                )
                            )
                        )
                    )
                );
            
        }

        [ComplexFormula(typeof(StatusFormula))]
        [EditorSequence(8)]
        public JobRequisitionItemStatus Status { get; set; }

        private class InStockFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                
                If<JobRequisitionItem, string, double>(
                    Property<JobRequisitionItem, string>(x => x.Dimensions.Unit.Conversion),
                    Condition.Equals,
                    Constant<JobRequisitionItem, string>(""),
                    ""
                ).Then(
                    Aggregate<StockMovement>(AggregateCalculation.Sum, x => x.Property(x => x.Units))
                        .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                ).Else(
                    Formula(FormulaOperator.Divide,
                        Aggregate<StockMovement>(AggregateCalculation.Sum, x => x.Property(x => x.Units))
                            .WithLink(x => x.JobRequisitionItem.ID, x => x.ID),
                        Property(x => x.Dimensions.Value)
                    )
                );
        }
        
        /// <summary>
        /// The amount of this requisition item that is currently in stock, which is an aggregate of the <see cref="StockMovement.Units"/> property.
        /// </summary>
        [ComplexFormula(typeof(InStockFormula))]
        [DoubleEditor(Editable = Editable.Disabled)]
        [EditorSequence(10)]
        public double InStock { get; set; }
        
        private class OnOrderFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                If<JobRequisitionItem, string, double>(
                    Property<JobRequisitionItem, string>(x => x.Dimensions.Unit.Conversion),
                    Condition.Equals,
                    Constant<JobRequisitionItem, string>(""),
                    ""
                ).Then(
                    Aggregate(
                            AggregateCalculation.Sum,
                            x => x.Property(x => x.Quantity),
                            new Filter<PurchaseOrderItemAllocation>(x => x.Item.ReceivedDate).IsEqualTo(null))
                        .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                        .WithLink(x => x.Item.Product.ID, x => x.Product.ID)
                ).Else(
                    Formula(FormulaOperator.Divide,
                        Aggregate(
                                AggregateCalculation.Sum,
                                x => x.Property(x => x.Quantity),
                                new Filter<PurchaseOrderItemAllocation>(x => x.Item.ReceivedDate).IsEqualTo(null))
                            .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                            .WithLink(x => x.Item.Product.ID, x => x.Product.ID),
                        Property(x => x.Dimensions.Value)
                    )
                );
        }
        [ComplexFormula(typeof(OnOrderFormula))]
        [DoubleEditor(Editable = Editable.Disabled)]
        [EditorSequence(11)]
        public double OnOrder { get; set; }
        
        private class GeneralOrderFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                Formula(
                    FormulaOperator.Add,

                    Aggregate<PurchaseOrderItem>(
                         AggregateCalculation.Sum,
                         x => x.Property(x => x.Unallocated),
                         new Filter<PurchaseOrderItem>(x => x.ReceivedDate).IsEqualTo(null)
                             .And(x => x.Job.ID).IsEqualTo(Guid.Empty)
                     )
                     .WithLink(x => x.Product.ID, x => x.Product.ID),

                    If<JobRequisitionItem, string, double>(
                        Property<JobRequisitionItem, string>(x => x.Dimensions.Unit.Conversion),
                        Condition.Equals,
                        Constant<JobRequisitionItem, string>(""),
                        ""
                    )
                    .Then(
                        
                        Aggregate(
                            AggregateCalculation.Sum,
                            x => x.Property(c => c.Quantity),
                            new Filter<PurchaseOrderItemAllocation>(x => x.Item.ReceivedDate).IsEqualTo(null)
                                .And(x => x.Job.ID).IsEqualTo(Guid.Empty)
                                .And(x => x.JobRequisitionItem.ID).IsEqualTo(Guid.Empty)
                        ).WithLink(x => x.Item.Product.ID, x => x.Product.ID)
                        
                    )
                    .Else(
                        Formula(
                            FormulaOperator.Divide,
                            Aggregate(
                                AggregateCalculation.Sum,
                                x => x.Property(c => c.Quantity),
                                new Filter<PurchaseOrderItemAllocation>(x => x.Item.ReceivedDate).IsEqualTo(null)
                                    .And(x => x.Job.ID).IsEqualTo(Guid.Empty)
                                    .And(x => x.JobRequisitionItem.ID).IsEqualTo(Guid.Empty)
                            ).WithLink(x => x.Item.Product.ID, x => x.Product.ID),
                            Property(x => x.Dimensions.Value)
                        )    
                    )

            );
        }
        [ComplexFormula(typeof(GeneralOrderFormula))]
        [DoubleEditor(Editable = Editable.Hidden)]
        public double GeneralOrder { get; set; }
        
        private class TotalOrdersFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                If<JobRequisitionItem, string, double>(
                    Property<JobRequisitionItem, string>(x => x.Dimensions.Unit.Conversion),
                    Condition.Equals,
                    Constant<JobRequisitionItem, string>(""),
                    ""
                ).Then(
                    Aggregate(
                            AggregateCalculation.Sum,
                            x => x.Property(x => x.Quantity),
                            new Filter<PurchaseOrderItemAllocation>(x => x.Item.ReceivedDate).IsEqualTo(null))
                        .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)

                ).Else(
                    Formula(FormulaOperator.Divide,
                        Aggregate(
                                AggregateCalculation.Sum,
                                x => x.Property(x => x.Quantity),
                                new Filter<PurchaseOrderItemAllocation>(x => x.Item.ReceivedDate).IsEqualTo(null))
                            .WithLink(x => x.JobRequisitionItem.ID, x => x.ID),
                        Property(x => x.Dimensions.Value)
                    )
                );
        }
        [ComplexFormula(typeof(TotalOrdersFormula))]
        [DoubleEditor(Editable = Editable.Disabled, Visible = Visible.Optional)]
        [EditorSequence(12)]
        public double TotalOrders { get; set; }
        
        private class TreatmentRequiredFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                Formula(
                    FormulaOperator.Subtract, 
                    Property(x => x.InStock), 
                    Property(x => x.Allocated),
                    Formula(
                        FormulaOperator.Subtract,
                        Property(x => x.TotalOrders),
                        Property(x => x.OnOrder)
                    )
                );
        }
        [ComplexFormula(typeof(TreatmentRequiredFormula))]
        [DoubleEditor(Editable = Editable.Disabled)]
        [EditorSequence(13)]
        public double TreatmentRequired { get; set; }

        private class TreatmentOnOrderFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                Formula(FormulaOperator.Subtract, Property(x => x.TotalOrders), Property(x => x.OnOrder));
        }
        [ComplexFormula(typeof(TreatmentOnOrderFormula))]
        [DoubleEditor(Editable = Editable.Disabled)]
        [EditorSequence(14)]
        public double TreatmentOnOrder { get; set; }

        private class AllocatedFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                Formula(
                    FormulaOperator.Subtract,
                    Formula(
                        FormulaOperator.Add,
                        If<JobRequisitionItem, string, double>(
                            Property<JobRequisitionItem, string>(x => x.Dimensions.Unit.Conversion),
                            Condition.Equals,
                            Constant<JobRequisitionItem, string>(""),
                            ""
                        ).Then(
                            Aggregate<StockMovement>(
                                    AggregateCalculation.Sum,
                                    x => x.Property(x => x.Units))
                                .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                                .WithLink(x => x.Style.ID, x => x.Style.ID)
                        ).Else(
                            Formula(FormulaOperator.Divide,
                                Aggregate<StockMovement>(
                                        AggregateCalculation.Sum,
                                        x => x.Property(x => x.Units))
                                    .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                                    .WithLink(x => x.Style.ID, x => x.Style.ID),
                                Property(x => x.Dimensions.Value)
                            )
                        ),
                        Property(x => x.OnOrder)
                    ),
                    Property(x => x.TotalOrders)
                );
        }
        [ComplexFormula(typeof(AllocatedFormula))]
        [DoubleEditor(Editable = Editable.Disabled)]
        [EditorSequence(15)]
        public double Allocated { get; set; }
        
        private class PickRequestedFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                If<JobRequisitionItem, string, double>(
                    Property<JobRequisitionItem, string>(x => x.Dimensions.Unit.Conversion),
                    Condition.Equals,
                    Constant<JobRequisitionItem, string>(""),
                    ""
                ).Then(
                    Aggregate<RequisitionItem>(
                            AggregateCalculation.Sum,
                            x => x.Property(x => x.Quantity),
                            new Filter<RequisitionItem>(x => x.RequisitionLink.StockUpdated).IsEqualTo(
                                DateTime.MinValue)
                        )
                        .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                ).Else(
                    Formula(FormulaOperator.Divide,
                        Aggregate<RequisitionItem>(
                                AggregateCalculation.Sum,
                                x => x.Property(x => x.Quantity),
                                new Filter<RequisitionItem>(x => x.RequisitionLink.StockUpdated).IsEqualTo(
                                    DateTime.MinValue)
                            )
                            .WithLink(x => x.JobRequisitionItem.ID, x => x.ID),
                        Property(x => x.Dimensions.Value)
                    )
                );
        }
        [ComplexFormula(typeof(PickRequestedFormula))]
        [DoubleEditor(Editable = Editable.Disabled)]
        [EditorSequence(16)]
        public double PickRequested { get; set; }
        
        private class IssuedFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                If<JobRequisitionItem, string, double>(
                    Property<JobRequisitionItem, string>(x => x.Dimensions.Unit.Conversion),
                    Condition.Equals,
                    Constant<JobRequisitionItem, string>(""),
                    ""
                ).Then(
                    Aggregate<StockMovement>(
                            AggregateCalculation.Sum,
                            x => x.Property(x => x.Issued),
                            new Filter<StockMovement>(x => x.Type).IsEqualTo(StockMovementType.Issue))
                        .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                        .WithLink(x => x.Style.ID, x => x.Style.ID)
                ).Else(
                    Formula(FormulaOperator.Divide,
                        Aggregate<StockMovement>(
                                AggregateCalculation.Sum,
                                x => x.Property(x => x.Issued),
                                new Filter<StockMovement>(x => x.Type).IsEqualTo(StockMovementType.Issue))
                            .WithLink(x => x.JobRequisitionItem.ID, x => x.ID)
                            .WithLink(x => x.Style.ID, x => x.Style.ID),
                        Property(x => x.Dimensions.Value)
                    )
                );
        }
        [ComplexFormula(typeof(IssuedFormula))]
        [DoubleEditor(Editable = Editable.Disabled)]
        [EditorSequence(17)]
        public double Issued { get; set; }
        
                
        private class TotalIssuedFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                Formula(FormulaOperator.Multiply, Property(x => x.Issued), Property(x => x.Dimensions.Value));
        }
        [DoubleEditor(Editable = Editable.Hidden)]
        [ComplexFormula(typeof(TotalIssuedFormula))]
        public double TotalIssued { get; set; }
        
        private class TotalAllocatedFormula : ComplexFormulaGenerator<JobRequisitionItem, double>
        {
            public override IComplexFormulaNode<JobRequisitionItem, double> GetFormula() =>
                Formula(FormulaOperator.Multiply, Property(x => x.Allocated), Property(x => x.Dimensions.Value));
        }
        [DoubleEditor(Editable = Editable.Hidden)]
        [ComplexFormula(typeof(TotalAllocatedFormula))]
        public double TotalAllocated { get; set; }
        
        [EntityRelationship(DeleteAction.SetNull)]
        [RequiredColumn]
        [Obsolete("Replaced with JobRequisitionItemPurchaseOrderItem")]
        [NullEditor]
        public PurchaseOrderItemLink PurchaseOrderItem { get; set; }

        private class PurchaseOrderNumbersFormula : ComplexFormulaGenerator<JobRequisitionItem, string>
        {
            public override IComplexFormulaNode<JobRequisitionItem, string> GetFormula() =>
                Aggregate<PurchaseOrderItemAllocation>(
                    AggregateCalculation.Concat,
                    x => x.Property(x => x.Item.PurchaseOrderLink.PONumber))
                .WithLink(x => x.JobRequisitionItem.ID, x => x.ID);
        }
        [ComplexFormula(typeof(PurchaseOrderNumbersFormula))]
        [TextBoxEditor(Editable = Editable.Hidden)]
        public string PurchaseOrderNumbers { get; set; }

        [RequiredColumn]
        [EditorSequence(18)]
        public DateTime Cancelled { get; set; } = DateTime.MinValue;

        [RequiredColumn]
        [EditorSequence(19)]
        public DateTime Archived { get; set; } = DateTime.MinValue;

        [RequiredColumn]
        [Obsolete("Replaced with JobRequisitionItemPurchaseOrderItem")]
        [NullEditor]
        public DateTime Ordered { get; set; } = DateTime.MinValue;

        [RequiredColumn]
        [EditorSequence(20)]
        public DateTime OrderRequired { get; set; } = DateTime.MinValue;

        [NullEditor]
        [Obsolete("Replaced with Problem", true)]
        public string Issues { get; set; }
        
        [EditorSequence("Issues", 1)]
        public ManagedProblem Problem { get; set; }

        [NullEditor]
        public long Sequence { get; set; }

        static JobRequisitionItem()
        {
            LinkedProperties.Register<JobRequisitionItem, ProductStyleLink, Guid>(x => x.Product.DefaultInstance.Style, x => x.ID, x => x.Style.ID);
            LinkedProperties.Register<JobRequisitionItem, ProductStyleLink, String>(x => x.Product.DefaultInstance.Style, x => x.Code, x => x.Style.Code);
            LinkedProperties.Register<JobRequisitionItem, ProductStyleLink, String>(x => x.Product.DefaultInstance.Style, x => x.Description, x => x.Style.Description);
            
            LinkedProperties.Register<JobRequisitionItem, ProductInstanceLink, double>(x => x.Product.DefaultInstance, x => x.NettCost,
                x => x.UnitCost);

            StockEntity.LinkStockDimensions<JobRequisitionItem>();
            
        }
        
        private bool bChanging;
        
        protected override void DoPropertyChanged(string name, object? before, object? after)
        {
            if (bChanging)
                return;

            try
            {
                bChanging = true;

                if (name.Equals(nameof(Qty)) && after is double qty)
                    TotalCost = UnitCost * qty;
                
                else if (name.Equals(nameof(UnitCost)) && after is double cost)
                    TotalCost = cost * Qty;

                else if (name.Equals(nameof(TotalCost)) && after is double total)
                {
                    if (Qty == 0)
                        Qty = 1;
                    UnitCost = total / Qty;
                }
            }
            finally
            {
                bChanging = false;
            }
            base.DoPropertyChanged(name, before, after);
        }

        public class JRICostData
        {
            public Guid[] ProductIDs { get; set; } = Array.Empty<Guid>();
            
            public ProductInstance[] Instances { get; set; } = Array.Empty<ProductInstance>();

            public SupplierProduct[] SupplierProducts { get; set; } = Array.Empty<SupplierProduct>();
            
            public SupplierDiscount[] SupplierDiscounts { get; set; } = Array.Empty<SupplierDiscount>();
        }
        
        public static void UpdateCosts(IEnumerable<JobRequisitionItem> items, Dictionary<String,object?> changes, JRICostData? data = null)
        {
            
            void UpdateValue<TType>(JobRequisitionItem item,Expression<Func<JobRequisitionItem, TType>> property, TType value)
            {
                CoreUtils.MonitorChanges(
                    item, 
                    () => CoreUtils.SetPropertyValue(item, CoreUtils.GetFullPropertyName(property, "."), value),
                    changes
                );
            }
            
            var _items =  items as JobRequisitionItem[] ?? items.ToArray();
            var productids = _items.Where(x => x.Product.ID != Guid.Empty).Select(x => x.Product.ID).ToArray();
            
            if(productids.Length == 0)
                return;
            
            var jobids = _items.Where(x => x.Product.ID != Guid.Empty).Select(x => x.Job.ID).ToList().Append(Guid.Empty).Distinct().ToArray();
            var supplierIDs = _items.Where(x => x.Supplier.ID != Guid.Empty).Select(x => x.Supplier.ID).ToArray();
            
            var needProducts = data is null || productids.Any(x => !data.ProductIDs.Contains(x));
            
            SupplierProduct[] supplierProducts;
            SupplierDiscount[] supplierDiscounts;
            ProductInstance[] productInstances;
            
            if(needProducts)
            {
                MultiQuery query = new MultiQuery();
                
                query.Add(
                    new Filter<SupplierProduct>(x => x.Product.ID).InList(productids).And(x => x.SupplierLink.ID)
                        .InList(supplierIDs),
                    Columns.None<SupplierProduct>().Add(x => x.Product.ID)
                        .Add(x => x.SupplierLink.ID)
                        .Add(x => x.Style.ID)
                        .AddDimensionsColumns(x => x.Dimensions, Classes.Dimensions.ColumnsType.Local)
                        .Add(x => x.Job.ID)
                        .Add(x => x.SupplierCode)
                        .Add(x => x.SupplierDescription)
                        .Add(x=>x.TradePrice)
                        .Add(x => x.CostPrice)
                        .Add(x => x.DiscountGroup.ID)
                );

                query.Add(
                    new Filter<SupplierDiscount>(x => x.Job.ID).InList(jobids)
                        .And(x => x.Group.ID).InQuery(
                            new Filter<SupplierProduct>(x => x.Product.ID).InList(productids),
                            x => x.DiscountGroup.ID
                        )
                    , Columns.None<SupplierDiscount>()
                        .Add(x => x.Group.ID)
                        .Add(x => x.Job.ID)
                        .Add(x=>x.Group.Type)
                        .Add(x => x.Value)
                );
                

                if(needProducts)
                {
                    query.Add(
                        new Filter<ProductInstance>(x=>x.Product.ID).InList(productids),
                        Columns.None<ProductInstance>().Add(x=>x.Product.ID)
                            .Add(x => x.Style.ID)
                            .AddDimensionsColumns(x => x.Dimensions, Classes.Dimensions.ColumnsType.Local)
                            .Add(x => x.NettCost)
                    );
                }
                
                query.Query();

                supplierProducts = query.Get<SupplierProduct>().ToArray<SupplierProduct>();
                supplierDiscounts = query.Get<SupplierDiscount>().ToArray<SupplierDiscount>();
                productInstances = needProducts
                    ? query.Get<ProductInstance>().ToArray<ProductInstance>()
                    : data!.Instances;

                if(data != null)
                {
                    data.SupplierProducts = supplierProducts;
                    data.SupplierDiscounts = supplierDiscounts;
                    if (needProducts)
                    {
                        data.ProductIDs = productids;
                        data.Instances = productInstances;
                    }
                }
            }
            else
            {
                supplierProducts = data!.SupplierProducts;
                supplierDiscounts = data!.SupplierDiscounts;
                productInstances = data!.Instances;
            }

            foreach (var item in items)
            {
                if(supplierProducts != null)
                {
                    //Check Supplier / Job Specific Pricing
                    var supplierProduct = supplierProducts.FirstOrDefault(x =>
                        x.Product.ID == item.Product.ID
                        && x.SupplierLink.ID == item.Supplier.ID
                        && x.Style.ID == item.Style.ID
                        && x.Dimensions.Equals(item.Dimensions)
                        && x.Job.ID == item.Job.ID);
                    if (supplierProduct != null)
                    {
                        UpdateValue<double>(item, x => x.UnitCost, supplierProduct.CostPrice);
                        continue;
                    }
                    
                    // Check Supplier Pricing
                    supplierProduct = supplierProducts.FirstOrDefault(x =>
                        x.Product.ID == item.Product.ID
                        && x.SupplierLink.ID == item.Supplier.ID
                        && x.Style.ID == item.Style.ID
                        && x.Dimensions.Equals(item.Dimensions)
                        && x.Job.ID == Guid.Empty);
                    if (supplierProduct != null)
                    {
                        var discount = supplierDiscounts.FirstOrDefault(x=>x.Job.ID == item.Job.ID && x.Group.ID == supplierProduct.DiscountGroup.ID);
                        if (discount == null)
                            discount = supplierDiscounts.FirstOrDefault(x=>x.Job.ID == Guid.Empty && x.Group.ID == supplierProduct.DiscountGroup.ID);
                        if (discount != null)
                        {
                            if (discount.Group.Type == SupplierDiscountGroupType.Discount)
                            {
                                var disc = (100.0-discount?.Value ?? 0.00)/100.0;
                                UpdateValue<double>(item, x => x.UnitCost, supplierProduct.TradePrice * disc);
                            }
                            else
                                UpdateValue<double>(item, x => x.UnitCost, discount.Value);
                        }
                        else
                            UpdateValue<double>(item, x => x.UnitCost, supplierProduct.CostPrice);
                        
                        continue;
                    }
                }
                
                // Check Specific Product Instance
                var productInstance = productInstances.FirstOrDefault(x =>
                    x.Product.ID == item.Product.ID
                    && x.Style.ID == item.Style.ID
                    && x.Dimensions.Equals(item.Dimensions));
                if (productInstance != null)
                    UpdateValue<double>(item, x => x.UnitCost, productInstance.NettCost);
                else
                    UpdateValue<double>(item, x => x.UnitCost, 0.0F);

            }
        }
    }
}