using CAPI.Custom.Data;
using CAPI.Custom.Data.Repositories;
using CAPI.Custom.Domain.Quotes;
using CAPI.Custom.Entities;
using framework_business;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;

namespace CAPI.Custom.Repositories
{
    public class QuoteRepository : SurfaceEntityRepository<Quote>
    {
        public QuoteRepository(QuotesContext context)
        {
            this.db = context ?? new QuotesContext();
        }

        public QuoteRepository() { }

        #region Child Collections

        public IQueryable<QuoteEnquiryItem> GetEnquiryItems(int quoteId) =>
            GetChildren<QuoteEnquiryItem>(quoteId)
            .OrderBy(p => p.Sequence);

        public IQueryable<QuoteRequestItem> GetRequestItems(int quoteId) =>
            GetChildren<QuoteRequestItem>(quoteId)
            .OrderBy(p => p.Sequence);

        public IQueryable<QuoteItem> GetItems(int quoteId) =>
            GetChildren<QuoteItem>(quoteId)
            .Include(p => p.PriceChecks)
            .Where(p => !p.IsCATList.Value)
            .OrderBy(p => p.Sequence)
            .ThenBy(p => p.SupplierSequence)
            .ThenBy(p => p.Supplier);

        protected IQueryable<QuoteItem> GetItemsInternal(int quoteId) =>
            GetChildren<QuoteItem>(quoteId)
            .Where(p => !p.IsCATList.Value)
            .OrderBy(p => p.Sequence)
            .ThenBy(p => p.SupplierSequence)
            .ThenBy(p => p.Supplier);

        public QuoteItem GetItemByItemId(int itemId) => Context.Set<QuoteItem>().GetFirstByItemId(itemId);

        public IQueryable<QuoteBuyoutSupplier> GetBuyoutSuppliers(int quoteId) =>
            GetChildren<QuoteBuyoutSupplier>(quoteId)
            .OrderBy(p => p.TradingName);

        public IQueryable<QuoteItemsFinal> GetFinalItems(int quoteId) =>
            GetChildren<QuoteItemsFinal>(quoteId)
            .OrderBy(p => p.Sequence);

        public IQueryable<QuoteBuyoutItem> GetBuyoutItems(int buyoutSupplierId) =>
            db.QuoteBuyoutItems.GetByParentId(buyoutSupplierId)
            .OrderBy(p => p.Sequence);

        public QuoteBuyoutSupplier GetBuyoutSupplierByItemId(int itemId) =>
            Context.Set<QuoteBuyoutSupplier>().GetFirstByItemId(itemId);

        #endregion

        #region Other Queries

        public IEnumerable<string> GetCreatedByUserDisplays() => Entities
            .Select(p => p.CreatedByDisplay).Distinct().ToList()
            .Where(p => p.IsNotEmpty()).OrderBy(p => p);

        public IEnumerable<string> GetCustomerTradingNames() => Entities
            .Select(p => p.CustomerTradingName).Distinct().ToList()
            .Where(p => p.IsNotEmpty()).OrderBy(p => p);

        public IEnumerable<string> GetStatuses() => new SurfaceLookupRepository().QuoteStatuses.Select(p => p.display);

        public Sale GetSale(int quoteId) => Context.Set<Sale>().FirstOrDefault(x => x.surfaceItemId == quoteId);
        #endregion

        public int GetNextSalesInvoiceNumber()
        {
            var setup = db.Setup;

            int invoiceNo = Context.Database.SqlQuery<int>("sp_GetInvoiceNoSales @ItemType",
                new SqlParameter("@ItemType", "QT"))
                .FirstOrDefault();

            // CVH 2016-09-14 Renamed to Next Num, updated with each transaction save, 
            // result and NextNum should technically always be equal
            int saleQTENextNum = setup.saleQTENextNum.GetValueOrDefault();

            if (invoiceNo < saleQTENextNum)//then use the start number
                invoiceNo = saleQTENextNum;

            return invoiceNo;
        }

        public string GetQuoteNumberFromInvoiceNumber(int invoiceNumber)
        {
            string quoteNumber = Context.Setup.saleQTEPrefix;
            int saleQTENumLength = Context.Setup.saleQTENumLength.GetValueOrDefault();
            quoteNumber = quoteNumber.PadRight(saleQTENumLength - invoiceNumber.ToString().Length, '0');
            quoteNumber += invoiceNumber.ToString();
            return quoteNumber;
        }

        public QuoteRepository CreateSaleAndExtraNote(int quoteId, int invoiceNumber)
        {
            var quote = GetByItemId(quoteId);

            Sale salesQuote = new Sale
            {
                accountNo = quote.CustomerCode,
                comments = quote.FinalNotes,
                dateOfCapture = DateTime.Now,
                dateOfService = quote.Date,
                invoiceNo = invoiceNumber,
                itemType = "QT",
                isVisible = true,
                name = quote.CustomerTradingName,
                postalAddress = quote.CustomerPostalAddress,
                statusId = (int)pNums.SalesSatus.Draft,
                surfaceItemId = quote.itemID,
                surname = quote.CustomerContactName,
                vatNum = quote.CustomerVATNumber,
                email = quote.CustomerContactEmail,
            };

            SalesExtraNote extraNote = new SalesExtraNote
            {
                note = quote.FinalNotes,
                type = pNums.DocumentType.Quote.GetHashCode(),
                number = salesQuote.invoiceNo,
                dateSaved = DateTime.Now,
            };

            salesQuote = Context.Create(salesQuote);
            extraNote = Context.Create(extraNote);
            return this;
        }

        public QuoteRepository UpdateSaleAndExtraNote(int quoteId)
        {
            var quote = GetByItemId(quoteId);
            var salesQuote = db.Sales.FirstOrDefault(p => p.surfaceItemId == quote.itemID);

            if (salesQuote.IsNotNull())
            {
                salesQuote.postalAddress = quote.CustomerPostalAddress;
                salesQuote.accountNo = quote.CustomerCode;
                salesQuote.name = quote.CustomerTradingName;
                salesQuote.surname = quote.CustomerContactName;
                salesQuote.comments = quote.FinalNotes;
                salesQuote.statusId = quote.StatusId;

                var extraNote = db.SalesExtraNotes.FirstOrDefault(p => p.number == salesQuote.invoiceNo);

                if (extraNote.IsNotNull())
                {
                    extraNote.note = quote.FinalNotes;
                    extraNote.dateSaved = DateTime.Now;
                }
            }

            return this;
        }

        #region overrides

        public override Quote GetByItemId(int itemId)
        {
            var quote = Entities.Include(p => p.QuoteStatus).FirstOrDefault(p => p.itemID == itemId);

            if (quote.CustomerVATRate.IsEmpty())
            {
                quote.CustomerVATRate = Context.Setup.vatRate.ToString();
            }

            return quote;
        }

        public override void Edit(Quote entity)
        {
            entity.LastUpdatedBy = User.recId;
            entity.LastUpdatedByDisplay = User.userDisplay;
            entity.LastUpdatedDate = DateTime.Now;
            base.Edit(entity);
        }

        public override Quote Update(Quote entity)
        {
            entity.LastUpdatedBy = User.recId;
            entity.LastUpdatedByDisplay = User.userDisplay;
            entity.LastUpdatedDate = DateTime.Now;
            return base.Update(entity);
        }

        public override IEnumerable<Quote> Update(IEnumerable<Quote> entities)
        {
            var user = GetUserById(User.recId);

            foreach (var entity in entities)
            {
                entity.LastUpdatedBy = user.recId;
                entity.LastUpdatedByDisplay = user.userDisplay;
                entity.LastUpdatedDate = DateTime.Now;
            }

            return base.Update(entities);
        }

        public override int Delete(Quote entity)
        {
            var quote = GetByItemId(entity.itemID);
            var quoteId = quote.itemID;

            // exit if quoteId does not exist
            if (quote.IsNull()) return 0;

            // gather all quote related information
            var buyoutSuppliers = db.QuoteBuyoutSuppliers.Where(p => quoteId == p.ParentSurfaceItemId);
            var buyoutSupplierIds = buyoutSuppliers.Select(p => p.itemID).ToArray();
            var buyoutItems = db.QuoteBuyoutItems.Where(p => buyoutSupplierIds.Contains(p.ParentSurfaceItemId.Value));
            var enquiryItems = db.QuoteEnquiryItems.Where(p => quoteId == p.ParentSurfaceItemId);
            var requestItems = db.QuoteRequestItems.Where(p => quoteId == p.ParentSurfaceItemId);
            var quoteItems = db.QuoteItems.Where(p => quoteId == p.ParentSurfaceItemId);
            var finalItems = db.QuoteItemsFinal.Where(p => quoteId == p.ParentSurfaceItemId);

            // remove collections that have items
            if (finalItems.Any()) db.QuoteItemsFinal.RemoveRange(finalItems);
            if (buyoutItems.Any()) db.QuoteBuyoutItems.RemoveRange(buyoutItems);
            if (buyoutSuppliers.Any()) db.QuoteBuyoutSuppliers.RemoveRange(buyoutSuppliers);
            if (quoteItems.Any()) db.QuoteItems.RemoveRange(quoteItems);
            if (requestItems.Any()) db.QuoteRequestItems.RemoveRange(requestItems);
            if (enquiryItems.Any()) db.QuoteEnquiryItems.RemoveRange(enquiryItems);

            // remove sales information
            var sale = db.Sales.FirstOrDefault(p => p.surfaceItemId == quoteId);

            if (sale.IsNotNull())
            {
                var extraNote = db.SalesExtraNotes.FirstOrDefault(p => p.number == sale.invoiceNo);

                if (extraNote.IsNotNull())
                {
                    db.SalesExtraNotes.Remove(extraNote);
                }

                db.Sales.Remove(sale);
            }

            // remove quote
            db.Quotes.Remove(quote);

            return db.SaveChanges();
        }

        #endregion

        public decimal GetVATRate(int quoteId)
        {
            var vatRate = (GetByItemId(quoteId)?.CustomerVATRate.TryParseDecimal()
                ?? Setup.vatRate).GetValueOrDefault();

            return vatRate;
        }

        public QuoteRepository RecalculateItemsTotals(int quoteId)
        {
            var totals = new QuoteItemsTotals(0);

            var setup = db.Setup;
            var quote = db.Quotes.FirstOrDefault(p => p.itemID == quoteId);

            if (quote.IsNull()) return null;

            // customer set vat rate
            var vatRate = GetVATRate(quoteId);
            totals = new QuoteItemsTotals(vatRate);

            var quoteItems = db.QuoteItems
                .Where(p =>
                    p.isActive.Value
                    && p.ParentSurfaceItemId == quote.itemID
                    && p.QuantitySelected > 0
                )
                .ToList();

            foreach (QuoteItem quoteItem in quoteItems)
            {
                int quantity = quoteItem.QuantitySelected.GetValueOrDefault();
                if (quantity < 0) quantity = 0;

                var sellingPrice = quoteItem.SellingPrice.GetValueOrDefault();
                var costPrice = quoteItem.CostPrice.GetValueOrDefault();
                var weightKG = quoteItem.WeightKG.GetValueOrDefault();

                totals.WeightKG += weightKG * (decimal)quantity;
                totals.Cost += costPrice * (decimal)quantity;
                totals.GrossProfit += (sellingPrice - costPrice) * (decimal)quantity;
                totals.VATExcluded += sellingPrice * (decimal)quantity;
            }

            quote.ItemsTotalWeightKG = totals.WeightKG;
            quote.ItemsTotalCost = totals.Cost;
            quote.ItemsTotalMargin = totals.Margin;
            quote.ItemsGrossProfit = totals.GrossProfit;
            quote.ItemsSubtotalexclVAT = totals.VATExcluded;
            quote.ItemsVAT = totals.VAT;
            quote.ItemsTotalinclVAT = totals.VATIncluded;

            Update(quote);
            return this;
        }

        #region Quote Items Methods

        public QuoteRepository RemoveMatchingSurplusWarehouseItems(int quoteId)
        {
            string supplierName = "SURPLUS WAREHOUSE";

            var masterGroups = (from qi in db.QuoteItems
                                where qi.isActive.Value
                                && qi.ParentSurfaceItemId == quoteId
                                && supplierName == qi.Supplier
                                orderby qi.CostPrice descending
                                group qi by qi.MasterPartNumber into grp
                                where grp.Count() > 1
                                select grp)
                                .ToList();

            foreach (var group in masterGroups)
            {
                var nullOr0 = new int?[] { null, 0 };
                var priciest = group.OrderByDescending(p => p.CostPrice).First();
                var toRemove = group.Where(p => p.itemID != priciest.itemID
                    // item has none selected
                    && nullOr0.Contains(p.QuantitySelected));
                if (toRemove.Any())
                {
                    db.Delete(toRemove);
                }
                //db.QuoteItems.RemoveRange(group.Where(p => p.itemID != priciest.itemID
                // item has none selected
                //&& nullOr0.Contains(p.QuantitySelected)));
            }

            return this;
        }

        //public QuoteRepository CheckPrevisoulyQuotedItemPrice(int quoteId)
        //{
        //    // TODO: Move withinDate to configuration
        //    var withinDate = DateTime.Now.AddMonths(-6);
        //    var currentQuote = GetByItemId(quoteId);
        //    var quoteItems = GetItems(quoteId).ToList();

        //    foreach (var currentItem in quoteItems)
        //    {
        //        currentItem.PriceCheckIsChecked = true;
        //        currentItem.PriceCheckCheckDate = DateTime.Now;

        //        var prevQuotedItem = (from item in db.QuoteItems
        //                              join quote in db.Quotes on item.ParentSurfaceItemId equals quote.itemID
        //                              where quote.CustomerTradingName == currentQuote.CustomerTradingName
        //                              && item.MasterPartNumber == currentItem.MasterPartNumber
        //                              && item.Condition == currentItem.Condition
        //                              && item.QuantitySelected > 0
        //                              && quote.Date.Value <= currentQuote.Date.Value
        //                              && quote.itemID < currentQuote.itemID
        //                              && quote.Date.Value >= withinDate
        //                              && quote.StatusId != QuoteStatus.Accepted
        //                              orderby quote.itemID descending,
        //                              item.SellingPrice.Value descending
        //                              select new
        //                              {
        //                                  QuoteDate = quote.Date,
        //                                  QuoteNumber = quote.Number,
        //                                  QuoteItemId = item.itemID,
        //                                  SellPrice = item.SellingPrice
        //                              })
        //                         .FirstOrDefault();

        //        if (prevQuotedItem.IsNotNull())
        //        {
        //            currentItem.PriceCheckIsChecked = true;
        //            currentItem.PriceCheckNewSellPrice = prevQuotedItem.SellPrice.GetValueOrDefault();
        //            currentItem.PriceCheckOldSellPrice = currentItem.SellingPrice.GetValueOrDefault();
        //            currentItem.PriceCheckQuoteDate = prevQuotedItem.QuoteDate.GetValueOrDefault();
        //            currentItem.PriceCheckQuoteItemId = prevQuotedItem.QuoteItemId;
        //            currentItem.PriceCheckQuoteNumber = prevQuotedItem.QuoteNumber;

        //            currentItem.SellingPrice = currentItem.PriceCheckNewSellPrice.GetValueOrDefault();

        //            // recalculate margin
        //            currentItem.Margin = BusinessUtils.GetMargin(
        //                costPrice: currentItem.CostPrice,
        //                newSellingPrice: currentItem.SellingPrice);

        //            db.Update(currentItem);
        //        }
        //    }

        //    return this;
        //}

        public QuoteRepository CheckPrevisoulyQuotedItemPrice(int quoteId)
        {
            var currentQuote = GetByItemId(quoteId);

            if (currentQuote.StatusId == QuoteStatus.Accepted) return this;

            // TODO: Move withinDate to configuration
            var withinDate = DateTime.Now.AddMonths(-6);
            var quoteItems = GetItems(quoteId).ToList();

            foreach (var currentItem in quoteItems)
            {
                // Check previously quoted

                var prevQuotedItems = (from item in db.QuoteItems
                                       join quote in db.Quotes on item.ParentSurfaceItemId equals quote.itemID
                                       where quote.CustomerTradingName == currentQuote.CustomerTradingName
                                       && item.MasterPartNumber == currentItem.MasterPartNumber
                                       && item.Condition == currentItem.Condition
                                       && item.QuantitySelected > 0
                                       && quote.Date.Value <= currentQuote.Date.Value
                                       && quote.itemID != currentQuote.itemID
                                       && quote.Date.Value >= withinDate
                                       orderby quote.Date descending
                                       select new
                                       {
                                           PreviousQuoteItemId = item.itemID,
                                           QuoteDate = quote.Date.Value,
                                           QuoteNumber = quote.Number,
                                           SellPrice = item.SellingPrice.Value,
                                           CheckType = quote.StatusId == QuoteStatus.Accepted ? PriceCheckType.Purchased : PriceCheckType.Quoted
                                       }).ToList().Select(p => new QuoteItemPriceCheck()
                                       {
                                           PreviousQuoteItemId = p.PreviousQuoteItemId,
                                           QuoteDate = p.QuoteDate,
                                           QuoteNumber = p.QuoteNumber,
                                           SellPrice = p.SellPrice,
                                           CheckType = p.CheckType,
                                           OriginalSellPrice = currentItem.SellingPrice.Value,
                                           QuoteItemRecId = currentItem.recId
                                       }).ToList();

                if (prevQuotedItems.Any())
                {
                    foreach (var prevItem in prevQuotedItems)
                    {
                        var priceCheck = currentItem.PriceChecks.FirstOrDefault(p => p.PreviousQuoteItemId == prevItem.PreviousQuoteItemId);

                        if (priceCheck.IsNull())
                        {
                            currentItem.PriceChecks.Add(prevItem);
                        }
                        else
                        {
                            priceCheck.CheckType = prevItem.CheckType;
                            priceCheck.SellPrice = prevItem.SellPrice;
                        }
                    }

                    Save();
                }
                

                //if (prevQuotedItems.IsNotNull())
                //{
                //    db.Create(new QuoteItemPriceCheck()
                //    {
                //        PreviousQuoteItemId = prevQuotedItems.QuoteItemId,
                //        CheckType =  PriceCheckType.Quoted,
                //        QuoteDate = prevQuotedItems.QuoteDate.GetValueOrDefault(),
                //        QuoteItemRecId = currentItem.recId,
                //        QuoteNumber = prevQuotedItems.QuoteNumber,
                //        SellPrice = prevQuotedItems.SellPrice.GetValueOrDefault()
                //    });
                //}
            }

            return this;
        }

        public QuoteRepository ApplyCATListPriceToQuoteItems(int quoteId)
        {
            // CEP-106 - CAT List Logic
            // 1. Enquired Part Number IS SAME as Master Part Number and is an exact match on CAT List... give CAT List Price
            // 2. Enquired Part Number IS NOT SAME as Master Part Number, but is a match on CAT List... give CAT List Price equal to Part Enquiry
            // 3. Neigher of the above 1 or 2 is true, then give R0.00 as the CAT List Price

            var enquiryItems = GetEnquiryItems(quoteId).ToList();
            // Only CAT Items
            var catItems = db.QuoteItems.GetByParentId(quoteId).Where(p => p.IsCATList.Value).ToList();
            // CAT Items Excluded
            var quoteItems = GetItemsInternal(quoteId).ToList();

            foreach (var quoteItem in quoteItems)
            {
                var catListCostPrice = catItems
                    .Where(catItem => quoteItem.MasterPartNumber == catItem.MasterPartNumber)
                    .Select(catItem => catItem.CostPrice)
                    .Max().GetValueOrDefault();

                if (catListCostPrice == 0)
                {
                    catListCostPrice = (from p in db.QuoteItems
                                        where p.ParentSurfaceItemId == quoteId
                                        && p.MasterPartNumber == quoteItem.MasterPartNumber
                                        select p)
                                        .Select(p => p.CATListCostPrice).Max().GetValueOrDefault();
                }

                quoteItem.CATListCostPrice = enquiryItems.Any(p => p.PartNumber == quoteItem.MasterPartNumber) ? catListCostPrice : 0;

                /* CEP-97 - Quote Items (Sell Price Logic)
                 * 1. If the Customer has a Default Margin (apply margin to Cost Price)
                 * 2. If items quoted before (apply previously quoted Sell Price as is currently being done)
                 * 3. If brand new item that does not meet either of 1 or 2 above (apply the CAT List Price as the Sell Price) */

                if (quoteItem.PriceCheckIsChecked.HasValue && quoteItem.PriceCheckIsChecked.Value)
                {
                    if (!quoteItem.PriceCheckNewSellPrice.HasValue)
                    {
                        if ("NEW".Equals(quoteItem.Brand))
                        {
                            quoteItem.SellingPrice = quoteItem.CATListCostPrice;
                            quoteItem.Margin = BusinessUtils.GetMargin(quoteItem.CostPrice, quoteItem.SellingPrice);
                        }
                    }
                }

                Context.Update(quoteItem);
            }

            return this;
        }

        public QuoteRepository FixQuoteItemSorting(int quoteId)
        {
            var requestItems = GetRequestItems(quoteId).ToList();
            //var masterNumbers = GetRequestItems(quoteId).Select(p => p.MasterNumber).ToList();

            var quoteItems = db.QuoteItems.GetByParentId(quoteId).ToList();

            foreach (var item in quoteItems)
            {
                //item.Sequence = masterNumbers.IndexOf(item.MasterPartNumber) + 1;
                item.Sequence = requestItems.FirstOrDefault(p => p.MasterNumber == item.MasterPartNumber)?.Sequence ?? 0;
                Context.Update(item);
            }
            //Save();
            //db.Update(quoteItems);
            return this;
        }

        public QuoteRepository RecalculateQuoteItems(IEnumerable<QuoteItem> items)
        {
            if (items.Any())
            {
                items.ToList().ForEach(quoteItem =>
                {
                    var stored = Context.Set<QuoteItem>().GetFirstByItemId(quoteItem.itemID);
                    bool marginChanged = stored.Margin != quoteItem.Margin;
                    bool sellingPriceChanged = stored.SellingPrice != quoteItem.SellingPrice;

                    if (marginChanged)
                    {
                        // set new margin
                        stored.Margin = quoteItem.Margin;

                        // calculate new selling price
                        stored.SellingPrice = BusinessUtils.GetSellingPrice(
                            costPrice: stored.CostPrice,
                            newMargin: quoteItem.Margin);
                    }
                    else if (sellingPriceChanged)
                    {
                        // set new selling price
                        stored.SellingPrice = quoteItem.SellingPrice;

                        // calculate new margin
                        stored.Margin = BusinessUtils.GetMargin(
                            costPrice: stored.CostPrice,
                            newSellingPrice: quoteItem.SellingPrice);
                    }

                    stored.QuantitySelected = quoteItem.QuantitySelected;
                    Context.Update(stored);
                });
            }

            return this;
        }

        public QuoteRepository UpdateQuoteItemsFromBuyoutItems(int buyoutSupplierId)
        {
            var buyoutSupplier = GetBuyoutSupplierByItemId(buyoutSupplierId);
            var quote = GetByItemId(buyoutSupplier.ParentSurfaceItemId.GetValueOrDefault());
            var buyoutItems = GetBuyoutItems(buyoutSupplierId).ToList();
            var quoteItems = GetItems(buyoutSupplier.ParentSurfaceItemId.GetValueOrDefault());

            foreach(var buyoutItem in buyoutItems)
            {
                QuoteItem quoteItem = quoteItems.FirstOrDefault(p =>
                        p.MasterPartNumber == buyoutItem.MasterPartNumber
                        && p.Brand == buyoutItem.Brand
                        && p.Condition == buyoutItem.Condition
                        && p.Supplier == buyoutSupplier.TradingName)
                        ?? new QuoteItem();

                quoteItem.Margin = quote.ItemsDefaultMargin.TryParseDecimal();
                quoteItem.LeadTime = buyoutItem.Leadtime;
                quoteItem.CostPrice = buyoutItem.TotalCost;

                //decimal divideBy = 1m - (quoteItem.QuoteItems_QuoteItems_QuoteItemsMargin.GetValueOrDefault() / 100m);
                quoteItem.CalculateSellingPrice(newMargin: quoteItem.Margin);// QuoteItems_QuoteItems_QuoteItemsSellingPrice = divideBy == 0 ? 0 : quoteItem.QuoteItems_QuoteItems_QuoteItemsCostPrice / divideBy;
                quoteItem.QuantityAvailable = buyoutItem.QuantityAvailable;

                if (quoteItem.recId == 0)
                {
                    quoteItem.ParentSurfaceItemId = buyoutSupplier.ParentSurfaceItemId.GetValueOrDefault();
                    quoteItem.MasterPartNumber = buyoutItem.MasterPartNumber;
                    quoteItem.Brand = buyoutItem.Brand;
                    quoteItem.Condition = buyoutItem.Condition;
                    quoteItem.Description = buyoutItem.Description;
                    quoteItem.PartNumber = buyoutItem.PartNumber;
                    quoteItem.Supplier = buyoutSupplier.TradingName;
                    quoteItem.WeightKG = buyoutItem.WeightKG;
                    quoteItem.WarehouseLocation = string.Empty;
                    quoteItem.SupplierCurrency = buyoutSupplier.Currency;
                    quoteItem.Sequence = buyoutItem.Sequence;

                    /* CVH 2019-01-22 Set supplier sequence:
                        0 - Inventory Items (not used)
                        1 - CEP Inventory pricelist
                        2 - CAT pricelist
                        3 - Other pricelists in alpha order
                        4 - Buyout supplier lists in alpha order
                        */
                    quoteItem.SupplierSequence = 4;
                    quoteItem = Context.Create(quoteItem);
                }
                else
                {
                    quoteItem = Context.Update(quoteItem);
                }
            }

            return this;
        }

        public QuoteRepository RefreshItemsPriceAndAvailability(int quoteId)
        {
            // fetch all buyout suppliers in quote
            var buyoutSuppliers = GetBuyoutSuppliers(quoteId).ToList();

            // fetch all buyout items from all suppliers
            var buyoutSupplierIds = buyoutSuppliers.Select(p => p.itemID).ToArray();

            var buyoutItems = db.QuoteBuyoutItems
                .Where(p => p.isActive.Value && buyoutSupplierIds.Contains(p.ParentSurfaceItemId.Value))
                .ToList();

            // fetch quote items
            var quoteItems = GetItems(quoteId).Where(p => "CAT" != p.Supplier).ToList();

            // fetch all supplier items where part number is in quote
            List<PartPriceAvailabilityResult> priceListItems = GetSupplierItems(quoteId);

            quoteItems.ForEach(quoteItem =>
            {
                var buyoutSupplier = buyoutSuppliers.FirstOrDefault(p =>
                    p.TradingName
                    == quoteItem.Supplier);

                var lineItem = priceListItems.FirstOrDefault(p => p.Equals(quoteItem));

                if (lineItem.IsNotNull())
                {
                    var costPrice = BusinessUtils.ApplyExchangeRate(
                        lineItem.Price,
                        lineItem.Currency);

                    var availability = lineItem.Available;

                    if (buyoutSupplier.IsNotNull())
                    {
                        var buyoutItem = buyoutItems.FirstOrDefault(p =>
                            p.MasterPartNumber == quoteItem.MasterPartNumber
                            && p.PartNumber == quoteItem.PartNumber
                            && p.Brand == quoteItem.Brand
                            && p.Condition == quoteItem.Condition
                            && p.ParentSurfaceItemId == buyoutSupplier.itemID);

                        if (buyoutItem.IsNotNull())
                        {
                            if (availability != buyoutItem.QuantityAvailable.GetValueOrDefault()
                                || costPrice != buyoutItem.CostPriceZAR.GetValueOrDefault())
                            {
                                buyoutItem.CostPriceZAR = costPrice;
                                buyoutItem.QuantityAvailable = availability;

                                var supplierRate = buyoutSupplier.ExchangeRate
                                    .GetValueOrDefault();

                                if (supplierRate > 0)
                                {
                                    buyoutItem.CostPriceForex
                                        = costPrice / supplierRate;
                                }

                                // this method will do additional calculations and save the buyout item
                                // and the related quote item
                                RecalculateBuyoutItem(buyoutItem, buyoutSupplier: buyoutSupplier);
                            }
                        }
                    }
                    else // CEP Inventory Item
                    {
                        // update if any differences
                        if (availability != quoteItem.QuantityAvailable.GetValueOrDefault()
                            || costPrice != quoteItem.CostPrice.GetValueOrDefault())
                        {
                            quoteItem.QuantityAvailable = availability;
                            quoteItem.CostPrice = costPrice;

                            // update margin based on cost and selling prices
                            quoteItem.Margin = BusinessUtils.GetMargin(
                                costPrice: quoteItem.CostPrice,
                                newSellingPrice: quoteItem.SellingPrice);

                            Context.Update(quoteItem);
                        }
                    }
                }
            });

            return this;
        }

        #endregion

        #region Buyout Supplier

        public QuoteBuyoutSupplier CreateBuyoutSupplier(int quoteId, int supplierId)
        {
            var quote = GetByItemId(quoteId);
            var supplier = db.Set<Supplier>().GetFirstByItemId(supplierId);

            if (quote.IsNull()) throw new Exception($"Could not find Quote with itemID: {quoteId}");
            if (supplier.IsNull()) throw new Exception($"Could not find Supplier with itemID: {supplierId}");

            var buyoutSupplier = GetBuyoutSuppliers(quoteId)
                .FirstOrDefault(p => supplier.TradingName.Equals(p.TradingName));

            if (buyoutSupplier.IsNotNull()) return null;

            string currency = supplier.Currency;
            decimal exchangeRate = 0m;

            if (!new string[] { "", "ZAR " }.Contains(currency))
            {
                exchangeRate = db.ExchangeRates.FirstOrDefault(p =>
                p.ForexCurrency.Equals(currency))
                    ?.BuyExchangeRate.TryParseDecimal()
                    .GetValueOrDefault() ?? 0m;
            }

            var freight = db.Freights
                .FirstOrDefault(p =>
                    p.SupplierTradingName.Equals(
                    supplier.TradingName)
                )?.Freight.TryParseDecimal().GetValueOrDefault()
                ?? 0m;

            buyoutSupplier = new QuoteBuyoutSupplier()
            {
                isActive = true,
                ParentSurfaceItemId = quoteId,
                ContactPerson = supplier.Contacts,
                Currency = supplier.Currency,
                DefaultDiscount = supplier.DefaultDiscount,
                DefaultLeadtime = supplier.DefaultLeadtime,
                Duty = 0m,
                Email = supplier.PrimaryEmail,
                ExchangeRate = exchangeRate,
                Freight = freight,
                Notes = supplier.Notes,
                Telephone = supplier.Telephone,
                VATRate = supplier.VATRate.TryParseDecimal(),
                TradingName = supplier.TradingName,
                BuyoutQuoteType = BuyoutQuoteType.SelectedItems
            };

            buyoutSupplier = Context.Create(buyoutSupplier);
            return buyoutSupplier;
        }

        public QuoteBuyoutSupplier UpdateBuyoutSupplier(QuoteBuyoutSupplier buyoutSupplier)
        {
            return Context.Update(buyoutSupplier);
        }

        public IQueryable<QuoteBuyoutItem> CreateBuyoutItems(int buyoutSupplierId)
        {
            var nonStockOnlyTypeId = db.SurfaceLookups
                .Where(p =>
                    p.isActive.Value
                    && p.LookupCategory.lookupCategory.Equals("Buyout Quote Type", StringComparison.OrdinalIgnoreCase)
                    && "Non-Stock Only".Equals(p.display)
                )
                .Select(p => p.recId)
                .FirstOrDefault();

            var buyoutSupplier = GetBuyoutSupplierByItemId(buyoutSupplierId);
            var quoteId = buyoutSupplier.ParentSurfaceItemId.GetValueOrDefault();
            var supplierName = buyoutSupplier.TradingName.Trim();
            var exRate = buyoutSupplier.ExchangeRate.GetValueOrDefault();
            var nonStockOnly = nonStockOnlyTypeId.Equals(buyoutSupplier.BuyoutQuoteType.GetValueOrDefault());

            try
            {
                //CVH 2019-03-20 Get buyout items already saved, need to update existing, instead of deleting and inserting new
                var existBuyoutItems = GetBuyoutItems(buyoutSupplierId).ToList();

                //load supplier quote items, if existing
                var quoteItems = db.QuoteItems
                    .Where(p =>
                        p.isActive.Value
                        && p.ParentSurfaceItemId == quoteId
                        && p.Supplier == supplierName
                    ).ToList();

                //ArrayList quoteItems = xData.GetTypedByCriteriaSpecific("recId", typeof(oQuoteItems), "isActive,ParentSurfaceItemId,QuoteItems_QuoteItems_QuoteItemsSupplier", "1," + quoteItemId + "," + supplierName, "", "publ_", false);
                if (quoteItems.Any())
                {
                    foreach (var quoteItem in quoteItems)
                    {
                        var buyObj = new QuoteBuyoutItem();

                        var existingBuyoutItem = existBuyoutItems
                            .Where(p =>
                                p.MasterPartNumber == quoteItem.MasterPartNumber
                                && p.PartNumber == quoteItem.PartNumber
                            ).ToArray();

                        //CVH 2019-03-20 Find existing buyout item (if any)
                        for (int i = existBuyoutItems.Count - 1; i >= 0; i--)
                        {
                            var existItm = existBuyoutItems[i];

                            if (quoteItem.PartNumber == existItm.PartNumber &&
                                quoteItem.MasterPartNumber == existItm.MasterPartNumber)
                            {
                                buyObj = existItm;

                                //remove item from list, will Delete all left over existing items (in case quote items have been removed)
                                existBuyoutItems.RemoveAt(i);
                            }
                        }

                        //create new item if none exist yet, but don't update if it does exist, nothing to update
                        if (buyObj.recId <= 0)
                        {
                            var currency = quoteItem.SupplierCurrency;
                            decimal eRate = 0;

                            if (!new string[] { "", "ZAR" }.Contains(currency))
                            {
                                eRate = db.ExchangeRates.FirstOrDefault(p => p.ForexCurrency == currency)
                                    ?.BuyExchangeRate.TryParseDecimal()
                                    .GetValueOrDefault() ?? 0m;
                            }

                            decimal zar = quoteItem.CostPrice.GetValueOrDefault();
                            decimal forex = 0;

                            if (eRate != 0)
                            {
                                forex = zar / eRate;
                            }

                            decimal supplierDiscount = buyoutSupplier.DefaultDiscount
                                .TryParseDecimal().GetValueOrDefault();
                            decimal supplierDuty = buyoutSupplier.Duty
                                .GetValueOrDefault();
                            decimal supplierFreight = buyoutSupplier.Freight
                                .GetValueOrDefault();

                            decimal dutyCost = BusinessUtils.GetSellingPrice(zar, supplierDuty) - zar;
                            decimal freightCost = BusinessUtils.GetSellingPrice(zar, supplierFreight) - zar;
                            decimal discount = (zar * supplierDiscount / 100m);
                            decimal totalCost = zar + freightCost + dutyCost - discount;

                            buyObj.ParentSurfaceItemId = buyoutSupplierId;
                            buyObj.MasterPartNumber = quoteItem.MasterPartNumber;
                            buyObj.Brand = quoteItem.Brand;
                            buyObj.Condition = quoteItem.Condition;

                            buyObj.CostPriceZAR = zar;
                            buyObj.CostPriceForex = forex;
                            buyObj.Description = quoteItem.Description;

                            buyObj.Discount = supplierDiscount;
                            buyObj.DutyCost = dutyCost;
                            buyObj.FreightCost = freightCost;
                            buyObj.Leadtime = buyoutSupplier.DefaultLeadtime;
                            buyObj.PartNumber = quoteItem.PartNumber;
                            buyObj.QuantityAvailable = quoteItem.QuantityAvailable;
                            buyObj.QuantityRequired = quoteItem.QuantitySelected;
                            buyObj.TotalCost = totalCost;
                            buyObj.WeightKG = quoteItem.WeightKG;
                            buyObj.Sequence = quoteItem.Sequence;
                            buyObj.Selected = true;

                            buyObj = Context.Create(buyObj);
                        }
                    }
                }
                else
                {
                    // CVH 2019-01-24 Do Row number with order by master number desc, sequence number asc. 
                    // Otherwise it uses the wrong sequence number on the buyout item
                    string query = ";WITH cte AS " +
                                    "( " +
                                    "   SELECT *, " +
                                    "            ROW_NUMBER() " +
                                    "            OVER(PARTITION BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " +
                                    "            ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber DESC, QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence ASC) AS rn " +
                                    "   FROM     publ_QuoteRequestItems " +
                                    "   WHERE    isActive = 1 AND ParentSurfaceItemId = " + quoteId + " " +
                                    ") " +
                                    "SELECT QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG " +
                                    "        , QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence " +
                                    "        , SUM(QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired) AS QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired " +
                                    "FROM cte " +
                                    "WHERE rn = 1 " +
                                    "GROUP BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber " +
                                    "		,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber " +
                                    "		,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription " +
                                    "       ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG " +
                                    "       ,QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence " +
                                    "ORDER BY QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber ";

                    DataTable dtRequestItems = xData.GetCustomTypedTable(query);

                    //CVH 2019-03-22 If Non-stock only, get the list request items that are CEP Inventory items, need to remove those from buyout items
                    var dtCEPInventoryReqItems = new List<SupplierPricelistPart>();
                    //DataTable dtCEPInventoryReqItems = new DataTable();

                    if (nonStockOnly && dtRequestItems.IsNotNull() && dtRequestItems.Rows.Count > 0)
                    {
                        HashSet<string> requestItemsHash = new HashSet<string>();
                        //string requestItems = string.Empty;

                        foreach (DataRow row in dtRequestItems.Rows)
                        {
                            string partNumber = row.Field<string>("QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber");
                            requestItemsHash.Add(partNumber);
                        }

                        if (requestItemsHash.Any())
                        {
                            dtCEPInventoryReqItems = db.SupplierPricelistParts
                                .Where(p =>
                                    p.isActive.Value
                                    && "Current" == p.PricelistStatus
                                    && "CEP Inventory" == p.SupplierName
                                    && requestItemsHash.Contains(p.PartNumber)
                                ).ToList();
                        }
                    }

                    //load all requested items
                    foreach (DataRow reqItem in dtRequestItems.Rows)
                    {
                        string partNumber = reqItem.Field<string>("QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsPartNumber");
                        string masterNumber = reqItem.Field<string>("QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsMasterNumber");

                        //CVH 2019-03-20 Don't create a buyout item for a "blank" item
                        if (partNumber.IsEmpty() && "Not found".Equals(masterNumber))
                            continue;

                        // CVH 2019-03-22 If data in CEP list, non stock has been selected, 
                        // so check if item exists in CEP list, if so skip and don't add as buyout item
                        if (dtCEPInventoryReqItems.Any())
                        {
                            if (dtCEPInventoryReqItems.Any(p => p.PartNumber == partNumber))
                                continue;
                        }

                        var buyObj = new QuoteBuyoutItem();

                        //CVH 2019-03-20 Find existing buyout item (if any)
                        for (int i = existBuyoutItems.Count - 1; i >= 0; i--)
                        {
                            var existItm = existBuyoutItems[i];

                            if (partNumber == existItm.PartNumber &&
                                masterNumber == existItm.MasterPartNumber)
                            {
                                buyObj = existItm;

                                //remove item from list, will Delete all left over existing items (in case quote items have been removed)
                                existBuyoutItems.RemoveAt(i);
                            }
                        }

                        if (buyObj.recId <= 0)
                        {
                            decimal duty = BusinessUtils.GetSellingPrice(0, 0);
                            decimal freight = BusinessUtils.GetSellingPrice(0, 0);
                            decimal supplierDiscount = buyoutSupplier.DefaultDiscount.TryParseDecimal().GetValueOrDefault();

                            buyObj.ParentSurfaceItemId = buyoutSupplierId;
                            buyObj.MasterPartNumber = masterNumber;
                            buyObj.Brand = "CEP";
                            buyObj.Condition = string.Empty;
                            buyObj.CostPriceForex = 0;
                            buyObj.CostPriceZAR = 0;
                            buyObj.Description = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsDescription"].ToString();

                            buyObj.Discount = supplierDiscount;
                            buyObj.DutyCost = 0;
                            buyObj.FreightCost = 0;
                            buyObj.Leadtime = buyoutSupplier.DefaultLeadtime;
                            buyObj.PartNumber = partNumber;
                            buyObj.QuantityAvailable = 0;
                            int qty = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsQuantityRequired"].ToString().TryParseInt().GetValueOrDefault();
                            buyObj.QuantityRequired = qty;
                            buyObj.TotalCost = 0;
                            decimal kg = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsWeightKG"].ToString().TryParseDecimal().GetValueOrDefault();
                            buyObj.WeightKG = kg;
                            int sequence = reqItem["QuoteRequestItems_QuoteRequestItems_QuoteRequestItemsItemSequence"].ToString().TryParseInt().GetValueOrDefault();
                            buyObj.Sequence = sequence;
                            buyObj.Selected = true;
                            buyObj = Context.Create(buyObj);
                        }
                        else
                        {
                            //CVH 2019-03-22 Need to make all selected (in case deselected before clicking the Non-stock only button)
                            buyObj.isActive = true;
                            buyObj.Selected = true;
                            Context.Update(buyObj);
                        }
                    }
                }

                //CVH 2019-03-20 Remove all buyout items no longer in quote items list
                foreach (var remOjb in existBuyoutItems)
                {
                    foreach (oSurfaceItem remItm in xData.GetTypedByCriteriaSpecific("recId", typeof(oSurfaceItem), "recId", remOjb.itemID.ToString()))
                    {
                        remItm.isDeleted = true;
                        remItm.isActive = false;
                        xData.UpdateTyped("recId", remItm.recId.ToString(), typeof(oSurfaceItem), remItm);
                    }

                    xData.DeletePublishedSurfaceItem(remOjb.surfaceId, remOjb.itemID);
                }
            }
            catch (Exception ex)
            {
                BusinessUtils.HandleException(ex);
                throw;
            }

            return db.QuoteBuyoutItems.Where(p => p.ParentSurfaceItemId == buyoutSupplierId && p.isActive.Value)
                .OrderBy(p => p.Sequence);
        }

        public QuoteBuyoutItem UpdateBuyoutItem(QuoteBuyoutItem item)
        {
            item = Context.Update(item);
            //item = RecalculateBuyoutItem(item);
            return item;
        }

        public QuoteBuyoutItem RecalculateBuyoutItem(QuoteBuyoutItem item, QuoteBuyoutSupplier buyoutSupplier = null)
        {
            var stored = Context.Set<QuoteBuyoutItem>().GetFirstByItemId(item.itemID);

            if (buyoutSupplier.IsNull())
            {
                var supplierId = stored.ParentSurfaceItemId.GetValueOrDefault();
                buyoutSupplier = GetBuyoutSupplierByItemId(supplierId);
            }

            var supplierFreight = buyoutSupplier.Freight.GetValueOrDefault();
            var supplierDuty = buyoutSupplier.Duty.GetValueOrDefault();

            var storedAvailable = stored.QuantityAvailable.GetValueOrDefault();
            var storedCost = stored.TotalCost.GetValueOrDefault();

            decimal forex = item.CostPriceForex.GetValueOrDefault();
            decimal zar = item.CostPriceZAR.GetValueOrDefault();
            decimal discount = item.Discount.GetValueOrDefault();
            int qtyAvail = item.QuantityAvailable.GetValueOrDefault();

            decimal freight = 0;
            decimal duty = 0;
            decimal discountAmount = 0;
            decimal total = 0;

            if (!new string[] { "", "ZAR" }.Contains(buyoutSupplier.Currency))
            {
                zar = forex * buyoutSupplier.ExchangeRate.GetValueOrDefault();
            }

            freight = BusinessUtils.GetSellingPrice(zar, supplierFreight) - zar;
            duty = BusinessUtils.GetSellingPrice(zar, supplierDuty) - zar;
            discountAmount = (zar * discount / 100m);
            // changed variable discount for supplierDiscount
            total = zar + freight + duty - discountAmount;

            stored.Brand = item.Brand;
            stored.Condition = item.Condition;
            stored.CostPriceForex = forex;
            stored.CostPriceZAR = zar;
            stored.Discount = discount;
            stored.DutyCost = duty;
            stored.FreightCost = freight;
            stored.Leadtime = item.Leadtime;
            stored.QuantityAvailable = qtyAvail;
            stored.TotalCost = total;
            stored.Selected = item.Selected;

            stored = Context.Update(stored);

            return stored;
        }

        public QuoteRepository RecalculateBuyoutItems(int buyoutSupplierId)
        {
            var buyoutSupplier = GetBuyoutSupplierByItemId(buyoutSupplierId);
            var buyoutItems = GetBuyoutItems(buyoutSupplierId).ToList();

            foreach (var buyoutItem in buyoutItems)
            {
                RecalculateBuyoutItem(buyoutItem, buyoutSupplier);
            }

            return this;
        }

        #endregion

        public QuoteRepository RefreshFinalItems(int quoteId)
        {
            var updates = 0;
            var requestItems = GetRequestItems(quoteId).ToList();
            var finalItems = GetFinalItems(quoteId).ToList();
            var buyoutSuppliers = GetBuyoutSuppliers(quoteId).ToList();
            var buyoutItems = new List<QuoteBuyoutItem>();

            buyoutSuppliers.ForEach(p =>
            {
                buyoutItems.AddRange(GetBuyoutItems(p.itemID));
            });

            var quoteItems = GetItems(quoteId).Where(p => p.QuantitySelected > 0).ToList();

            HashSet<int> updatedFinalItems = new HashSet<int>();
            var finalItemIndex = 1;

            foreach (var requestItem in requestItems)
            {
                var qItems = quoteItems.Where(p =>
                       p.MasterPartNumber == requestItem.MasterNumber
                       && p.Sequence == requestItem.Sequence
                   ).ToList();

                foreach (var quoteItem in qItems)
                {
                    var finalItem = finalItems.FirstOrDefault(p => p.QuoteItemsLink == quoteItem.itemID)
                        ?? new QuoteItemsFinal()
                        {
                            isActive = true,
                            ParentSurfaceItemId = quoteId,
                            QuoteItemsLink = quoteItem.itemID
                        };

                    // TODO: we using this field?
                    finalItem.DimensionalDescription = string.Empty;

                    finalItem.Sequence = finalItemIndex++;

                    // fill or update from requestItem

                    finalItem.AdditionalDescription = requestItem.AdditionalDescription;
                    finalItem.QuantityRequired = requestItem.QuantitySelected;

                    // fill or update from quoteItem

                    finalItem.Brand = quoteItem.Brand;
                    finalItem.Category = quoteItem.Category;
                    finalItem.Condition = quoteItem.Condition;
                    finalItem.Description = quoteItem.Description;
                    finalItem.LeadTime = quoteItem.LeadTime;
                    finalItem.MasterPartNumber = quoteItem.MasterPartNumber;
                    finalItem.MinorCode = quoteItem.MinorCode;
                    finalItem.PartNumber = requestItem.PartNumber;
                    finalItem.SubCategory = quoteItem.Subcategory;
                    finalItem.TotalPriceZAR = quoteItem.SellingPrice * quoteItem.QuantitySelected;
                    finalItem.UniqueNumber = quoteItem.UniqueNumber;
                    finalItem.UnitPriceZAR = quoteItem.SellingPrice;
                    finalItem.WeightKG = quoteItem.WeightKG;
                    finalItem.QuoteItemsLink = quoteItem.itemID;
                    finalItem.Supplier = quoteItem.Supplier;
                    finalItem.Location = quoteItem.BinLocation;
                    finalItem.WarehouseLocation = quoteItem.WarehouseLocation;
                    finalItem.QuantityAvailable = quoteItem.QuantitySelected;

                    /* 
                     * CostPrizeZAR and CostPrizeForex
                     * 
                        - Items coming from INVENTORY have a Cost in Quote Items...that’s the actual cost.
                        - For Local Buyouts Forex will also be 0.
                        - But for Overseas Buyouts both the ZAR and Forex to be populated.
                        - The Purchase Order will use the field relevant to the Supplier currency BUT, 
                            we need both for later reporting.
                    */

                    var buyoutSupplier = buyoutSuppliers.FirstOrDefault(p => p.TradingName == quoteItem.Supplier);

                    // set value from quoteItem
                    finalItem.CostPriceZAR = quoteItem.CostPrice;

                    if (buyoutSupplier.IsNotNull())
                    {
                        var buyoutItem = buyoutItems.FirstOrDefault(p =>
                            p.ParentSurfaceItemId == buyoutSupplier.itemID
                            && quoteItem.MasterPartNumber == p.MasterPartNumber
                            && quoteItem.PartNumber == p.PartNumber
                            && quoteItem.Brand == p.Brand
                            && quoteItem.Condition == p.Condition
                        );

                        finalItem.ExchangeRate = buyoutSupplier.ExchangeRate;

                        if (buyoutItem.IsNotNull())
                        {
                            // set values from buyout supplier and item
                            finalItem.CostPriceZAR = buyoutItem.CostPriceZAR - buyoutItem.Discount;

                            // Overseas Buyouts both the ZAR and Forex to be populated
                            if (!"ZAR".EqualsIgnoreCase(buyoutSupplier.Currency))
                            {
                                finalItem.CostPriceFOREX = buyoutItem.CostPriceForex - buyoutItem.Discount;
                            }
                        }
                    }

                    finalItem = finalItem.IsNew
                         ? Context.Create(finalItem)
                         : Context.Update(finalItem);

                    updates++;
                    updatedFinalItems.Add(finalItem.itemID);
                }
            }

            var excludedFinalItems = finalItems.Where(p => !updatedFinalItems.Contains(p.itemID));

            // delete existing final items which related quote item is not selected
            // (quoteItem.SelectedQuantity <= 0)
            if (excludedFinalItems.Any())
            {
                foreach (var finalItem in excludedFinalItems)
                {
                    Context.Delete(finalItem);
                    updates++;
                }
            }

            return this;
        }

        public QuoteRepository RecalculateFinalTotals(int quoteId)
        {
            var quote = GetByItemId(quoteId);

            if (quote.IsNull()) return null;

            // customer set vat rate
            var vatRate = GetVATRate(quoteId);
            var totals = new QuoteItemsFinalTotals(vatRate);

            //recalculate totals
            var finalItems = GetFinalItems(quoteId).ToList();// db.QuoteItemsFinal.Where(p => p.isActive.Value && p.ParentSurfaceItemId == quoteId && p.Status);

            foreach (var f in finalItems)
            {
                int quantity = f.QuantityRequired.GetValueOrDefault();
                if (quantity < 0) quantity = 0;

                totals.WeightKG += f.WeightKG.GetValueOrDefault() * (decimal)quantity;
                totals.VATExcluded += f.TotalPriceZAR.GetValueOrDefault();
            }

            quote.FinalTotalWeightKG = totals.WeightKG;
            quote.FinalSubtotalexclVAT = totals.VATExcluded;
            quote.FinalVAT = totals.VAT;
            quote.FinalTotalinclVAT = totals.VATIncluded;

            Context.Update(quote);
            return this;
        }

        public QuoteBuyoutItem CloneBuyoutItem(int itemId)
        {
            var item = Context.Set<QuoteBuyoutItem>().GetFirstByItemId(itemId);
            QuoteBuyoutItem clone = null;

            if (item.IsNotNull())
            {
                clone = new QuoteBuyoutItem
                {
                    isActive = true,
                    ParentSurfaceItemId = item.ParentSurfaceItemId.GetValueOrDefault(),
                    MasterPartNumber = item.MasterPartNumber,
                    Brand = item.Brand,
                    Condition = item.Condition,
                    CostPriceForex = item.CostPriceForex.GetValueOrDefault(),
                    CostPriceZAR = item.CostPriceZAR,
                    Description = item.Description,
                    Discount = item.Discount,
                    DutyCost = item.DutyCost,
                    FreightCost = item.FreightCost,
                    Leadtime = item.Leadtime,
                    PartNumber = item.PartNumber,
                    QuantityAvailable = item.QuantityAvailable,
                    QuantityRequired = item.QuantityRequired,
                    Selected = item.Selected,
                    TotalCost = item.TotalCost,
                    WeightKG = item.WeightKG,
                    Sequence = item.Sequence,
                    surfaceId = item.surfaceId
                };

                clone = Context.Create(clone);
            }

            return clone;
        }

        protected List<PartPriceAvailabilityResult> GetSupplierItems(int quoteId)
        {
            string query = $@"
                SELECT 
                    itemID
	                ,PartNumber = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber
	                ,SupplierName = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSupplierName
	                ,Brand = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsBrand
	                ,Condition = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCondition
	                ,Available = ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsAvailability, 0)
	                ,Price = ISNULL(SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsSalesPrice, 0)
	                ,Currency = CASE 
		                WHEN SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency = '' 
		                THEN 'ZAR' 
		                ELSE SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsCurrency 
                    END
                    ,Note = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsNote
                FROM publ_SupplierPricelistParts 
                WHERE 
	                'Current' = SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPricelistStatus
	                AND SupplierPricelistParts_SupplierPricelistParts_SupplierPricelistPartsPartNumber IN (
			                SELECT DISTINCT(QuoteItems_QuoteItems_QuoteItemsPartNumber)
			                FROM publ_QuoteItems 
			                WHERE ParentSurfaceItemId = {quoteId}
		                )";

            var result = db.Database.SqlQuery<PartPriceAvailabilityResult>(query).ToList();
            return result;
        }

        public QuoteRepository EstimateDeliveryDate(int quoteId, DateTime? acceptedDate = null)
        {
            var initDate = acceptedDate ?? DateTime.Now;
            var quote = GetByItemId(quoteId);

            if ("ACCEPTED".Equals(quote.QuoteStatus.display.ToUpper())) return this;

            //if (!quote.DeliveryEstimatedDate.HasValue)
            //{

            if (initDate.IsNull()) return this;
            //throw new ArgumentException("Missing AcceptedDate", "Quote.AcceptedDate");

            var maxLeadTime = GetFinalItems(quoteId)
               .Select(p => p.LeadTime)
               .Distinct()
               // Only parameterless constructors and initializers are supported in LINQ to Entities
               .ToList()
               .Select(p => new LeadTime(p))
               .Max();

            if (maxLeadTime.IsNotNull())
            {
                var estimatedDeliveryDate = initDate.Add(maxLeadTime.ToTimeSpan());
                quote.DeliveryEstimatedDate = initDate.Add(maxLeadTime.ToTimeSpan());
                Context.Update(quote);
            }
            //}

            return this;
        }
    }
}