public virtual void ImportPhilatelicProductsFromXlsx(Stream stream)
        {
            var CurrentCustomer = _authenticationService.GetAuthenticatedCustomer();
            string logError = string.Format("Lot Import: Beginning Lot Import by User: {0}.", CurrentCustomer.Username);
            _logger.InsertLog(LogLevel.Information, logError);
            
            //Get all of the specification attributes so you can get ids for spec attribute and option id later
            bool optionExists = false;
            bool specExists = false;

            int specMaterialType = 0;
            int specMaterialTypeDisplayOrder = 0;
            int specFormat = 0;
            int specFormatDisplayOrder = 0;
            int specFormFactor = 0;
            int specFormFactorDisplayOrder = 0;
            int specUsageState = 0;
            int specUsageStateDisplayOrder = 0;
            int specGrade = 0;
            int specGradeDisplayOrder = 0;
            int specSymbolSummary = 0;
            int specSymbolSummaryDisplayOrder = 0;

            // int saleErrorCnt = 0;
            //bool junctionExists = false;
            AUSaleLotRecord sl = null;
            AUSaleRecord csale = null;


            var specAttributes = _specificationAttributeRepo.Table.ToList();
                foreach (SpecificationAttribute sa in specAttributes)
            {
                //Names are kept with space between words for customer display purposes - internal fields remove space
                switch (sa.Name) {
                    case "Material Type":
                        specMaterialType = sa.Id;
                        specMaterialTypeDisplayOrder = sa.DisplayOrder;
                        break;
                    case "Format": 
                        specFormat = sa.Id;
                        specFormatDisplayOrder = sa.DisplayOrder;
                        break;
                    case "Form Factor": 
                        specFormFactor = sa.Id;
                        specFormFactorDisplayOrder = sa.DisplayOrder;
                        break;
                    case "Usage State": 
                        specUsageState = sa.Id;
                        specUsageStateDisplayOrder = sa.DisplayOrder;
                        break;
                    case "Grade": 
                        specGrade = sa.Id;
                        specGradeDisplayOrder = sa.DisplayOrder;
                        break;
                    case "Symbol Summary":
                        specSymbolSummary = sa.Id;
                        specSymbolSummaryDisplayOrder = sa.DisplayOrder;
                        break;
                    default: 
                        logError = string.Format("Lot Import: A product specification attribute was found that is not accounted for in the import: {0}/Id:{1}.", sa.Name, sa.Id);
                        _logger.InsertLog(LogLevel.Warning, logError);
                        break;
                }

            }


            // ok, we can run the real code of the sample now
            using (var xlPackage = new ExcelPackage(stream))
            {
                // get the first worksheet in the workbook
                var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    throw new NopException("No Philatelic worksheet found");

                int totalerrors = 0;
                int recordsaddchanged = 0;
                int recordsignored = 0;

                //the columns
                var properties = new[]
                {
                    "ProductTypeId",
                    "ParentGroupedProductId",
                    "VisibleIndividually",
                    "Name",
                    "ShortDescription",
                    "FullDescription",
                    "VendorId",
                    "ProductTemplateId",
                    "ShowOnHomePage",
                    "MetaKeywords",
                    "MetaDescription",
                    "MetaTitle",
                    "SeName",
                    "AllowCustomerReviews",
                    "Published",
                    "SKU",
                    "ManufacturerPartNumber",
                    "Gtin",
                    "IsGiftCard",
                    "GiftCardTypeId",
                    "RequireOtherProducts",
                    "RequiredProductIds",
                    "AutomaticallyAddRequiredProducts",
                    "IsDownload",
                    "DownloadId",
                    "UnlimitedDownloads",
                    "MaxNumberOfDownloads",
                    "DownloadActivationTypeId",
                    "HasSampleDownload",
                    "SampleDownloadId",
                    "HasUserAgreement",
                    "UserAgreementText",
                    "IsRecurring",
                    "RecurringCycleLength",
                    "RecurringCyclePeriodId",
                    "RecurringTotalCycles",
                    "IsRental",
                    "RentalPriceLength",
                    "RentalPricePeriodId",
                    "IsShipEnabled",
                    "IsFreeShipping",
                    "ShipSeparately",
                    "AdditionalShippingCharge",
                    "DeliveryDateId",
                    "IsTaxExempt",
                    "TaxCategoryId",
                    "IsTelecommunicationsOrBroadcastingOrElectronicServices",
                    "ManageInventoryMethodId",
                    "UseMultipleWarehouses",
                    "WarehouseId",
                    "StockQuantity",
                    "DisplayStockAvailability",
                    "DisplayStockQuantity",
                    "MinStockQuantity",
                    "LowStockActivityId",
                    "NotifyAdminForQuantityBelow",
                    "BackorderModeId",
                    "AllowBackInStockSubscriptions",
                    "OrderMinimumQuantity",
                    "OrderMaximumQuantity",
                    "AllowedQuantities",
                    "AllowAddingOnlyExistingAttributeCombinations",
                    "DisableBuyButton",
                    "DisableWishlistButton",
                    "AvailableForPreOrder",
                    "PreOrderAvailabilityStartDateTimeUtc",
                    "CallForPrice",
                    "Price",
                    "OldPrice",
                    "ProductCost",
                    "SpecialPrice",
                    "SpecialPriceStartDateTimeUtc",
                    "SpecialPriceEndDateTimeUtc",
                    "CustomerEntersPrice",
                    "MinimumCustomerEnteredPrice",
                    "MaximumCustomerEnteredPrice",
                    "Weight",
                    "Length",
                    "Width",
                    "Height",
                    "CreatedOnUtc",
                    "CategoryIds",
                    "ManufacturerIds",
                    "Picture1",
                    "Picture2",
                    "Picture3",
                                                           
                    "SaleNbr",                  //NJM: BEGIN OF PHILATELIC PORTION
                    "LotNbr",
                    "DisplayOrder",
                    "ConsignmentId",
                    "MaterialType",
                    "Country1",
                    "StateProvince1",
                    "Country2",
                    "StateProvince2",
                    "Country3",
                    "StateProvince3",
                    "Format",
                    "FormFactor",
                    "UsageState",
                    "Grade",
                    "ReserveAmt",
                    "EstimatedLowBidAmt",
                    "EstimatedHighBidAmt",
                    "EstimatedRealizationAmt",
                    "OpeningBidAmt",
                    "SymbolSummary",
                    "ReferenceId1",
                    "ReferenceNbr1",
                    "ReferenceValue1",
                    "ReferenceId2",
                    "ReferenceNbr2",
                    "ReferenceValue2",
                    "ReferenceId3",
                    "ReferenceNbr3",
                    "ReferenceValue3"
                };

                
                //_logger.InsertLog(LogLevel.Information, "Finished Import mapping");

                int iRow = 2;
                while (true)
                {
                    totalerrors = 0; //reset initial edit-error count

                    bool allColumnsAreEmpty = true;
                    for (var i = 1; i <= properties.Length; i++)
                        if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString()))
                        {
                            allColumnsAreEmpty = false;
                            break;
                        }
                    if (allColumnsAreEmpty)
                    {
                       
                        logError = string.Format("Lot Import: Ignoring empty row#{0}.", iRow);
                        _logger.InsertLog(LogLevel.Information, logError);
                        break;
                    }

                   

                    //_logger.InsertLog(LogLevel.Information, "Import load data record:" + iRow.ToString());

                    int productTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductTypeId")].Value);
                    int parentGroupedProductId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ParentGroupedProductId")].Value);
                    bool visibleIndividually = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "VisibleIndividually")].Value);
                    string name = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Name")].Value);
                    string shortDescription = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ShortDescription")].Value);
                    string fullDescription = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "FullDescription")].Value);
                    int vendorId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "VendorId")].Value);
                    int productTemplateId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductTemplateId")].Value);
                    bool showOnHomePage = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "ShowOnHomePage")].Value);
                    string metaKeywords = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MetaKeywords")].Value);
                    string metaDescription = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MetaDescription")].Value);
                    string metaTitle = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MetaTitle")].Value);
                    string seName = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SeName")].Value);
                    bool allowCustomerReviews = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowCustomerReviews")].Value);
                    bool published = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "Published")].Value);
                    string sku = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SKU")].Value);
                    string manufacturerPartNumber = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ManufacturerPartNumber")].Value);
                    string gtin = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Gtin")].Value);
                    bool isGiftCard = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsGiftCard")].Value);
                    int giftCardTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "GiftCardTypeId")].Value);
                    bool requireOtherProducts = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "RequireOtherProducts")].Value);
                    string requiredProductIds = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "RequiredProductIds")].Value);
                    bool automaticallyAddRequiredProducts = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AutomaticallyAddRequiredProducts")].Value);
                    bool isDownload = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsDownload")].Value);
                    int downloadId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DownloadId")].Value);
                    bool unlimitedDownloads = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "UnlimitedDownloads")].Value);
                    int maxNumberOfDownloads = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "MaxNumberOfDownloads")].Value);
                    int downloadActivationTypeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DownloadActivationTypeId")].Value);
                    bool hasSampleDownload = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "HasSampleDownload")].Value);
                    int sampleDownloadId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "SampleDownloadId")].Value);
                    bool hasUserAgreement = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "HasUserAgreement")].Value);
                    string userAgreementText = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "UserAgreementText")].Value);
                    bool isRecurring = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsRecurring")].Value);
                    int recurringCycleLength = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringCycleLength")].Value);
                    int recurringCyclePeriodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringCyclePeriodId")].Value);
                    int recurringTotalCycles = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RecurringTotalCycles")].Value);
                    bool isRental = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsRental")].Value);
                    int rentalPriceLength = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RentalPriceLength")].Value);
                    int rentalPricePeriodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "RentalPricePeriodId")].Value);
                    bool isShipEnabled = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsShipEnabled")].Value);
                    bool isFreeShipping = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsFreeShipping")].Value);
                    bool shipSeparately = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "ShipSeparately")].Value);
                    decimal additionalShippingCharge = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "AdditionalShippingCharge")].Value);
                    int deliveryDateId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DeliveryDateId")].Value);
                    bool isTaxExempt = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsTaxExempt")].Value);
                    int taxCategoryId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "TaxCategoryId")].Value);
                    bool isTelecommunicationsOrBroadcastingOrElectronicServices = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsTelecommunicationsOrBroadcastingOrElectronicServices")].Value);
                    int manageInventoryMethodId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ManageInventoryMethodId")].Value);
                    bool useMultipleWarehouses = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "UseMultipleWarehouses")].Value);
                    int warehouseId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "WarehouseId")].Value);
                    int stockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "StockQuantity")].Value);
                    bool displayStockAvailability = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayStockAvailability")].Value);
                    bool displayStockQuantity = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayStockQuantity")].Value);
                    int minStockQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "MinStockQuantity")].Value);
                    int lowStockActivityId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "LowStockActivityId")].Value);
                    int notifyAdminForQuantityBelow = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "NotifyAdminForQuantityBelow")].Value);
                    int backorderModeId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "BackorderModeId")].Value);
                    bool allowBackInStockSubscriptions = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowBackInStockSubscriptions")].Value);
                    int orderMinimumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMinimumQuantity")].Value);
                    int orderMaximumQuantity = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "OrderMaximumQuantity")].Value);
                    string allowedQuantities = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowedQuantities")].Value);
                    bool allowAddingOnlyExistingAttributeCombinations = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AllowAddingOnlyExistingAttributeCombinations")].Value);
                    bool disableBuyButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableBuyButton")].Value);
                    bool disableWishlistButton = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "DisableWishlistButton")].Value);
                    bool availableForPreOrder = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "AvailableForPreOrder")].Value);
                    DateTime? preOrderAvailabilityStartDateTimeUtc = null;
                    var preOrderAvailabilityStartDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "PreOrderAvailabilityStartDateTimeUtc")].Value;
                    if (preOrderAvailabilityStartDateTimeUtcExcel != null)
                        preOrderAvailabilityStartDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(preOrderAvailabilityStartDateTimeUtcExcel));
                    bool callForPrice = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "CallForPrice")].Value);
                    decimal price = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Price")].Value);
                    decimal oldPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "OldPrice")].Value);
                    decimal productCost = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ProductCost")].Value);
                    decimal? specialPrice = null;
                    var specialPriceExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPrice")].Value;
                    if (specialPriceExcel != null)
                        specialPrice = Convert.ToDecimal(specialPriceExcel);
                    DateTime? specialPriceStartDateTimeUtc = null;
                    var specialPriceStartDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceStartDateTimeUtc")].Value;
                    if (specialPriceStartDateTimeUtcExcel != null)
                        specialPriceStartDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceStartDateTimeUtcExcel));
                    DateTime? specialPriceEndDateTimeUtc = null;
                    var specialPriceEndDateTimeUtcExcel = worksheet.Cells[iRow, GetColumnIndex(properties, "SpecialPriceEndDateTimeUtc")].Value;
                    if (specialPriceEndDateTimeUtcExcel != null)
                        specialPriceEndDateTimeUtc = DateTime.FromOADate(Convert.ToDouble(specialPriceEndDateTimeUtcExcel));

                    bool customerEntersPrice = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "CustomerEntersPrice")].Value);
                    decimal minimumCustomerEnteredPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "MinimumCustomerEnteredPrice")].Value);
                    decimal maximumCustomerEnteredPrice = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "MaximumCustomerEnteredPrice")].Value);
                    decimal weight = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Weight")].Value);
                    decimal length = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Length")].Value);
                    decimal width = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Width")].Value);
                    decimal height = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "Height")].Value);
                    DateTime createdOnUtc = DateTime.FromOADate(Convert.ToDouble(worksheet.Cells[iRow, GetColumnIndex(properties, "CreatedOnUtc")].Value));
                    string categoryIds = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "CategoryIds")].Value);
                    string manufacturerIds = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ManufacturerIds")].Value);
                    string picture1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Picture1")].Value);
                    string picture2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Picture2")].Value);
                    string picture3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Picture3")].Value);

                    //NJM: BEGIN OF PHILATELIC PORTION
                    string SaleNbr = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SaleNbr")].Value);
                    int LotNbr = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "LotNbr")].Value);                //will only be processed if Sale present and found 
                    int DisplayOrder = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "DisplayOrder")].Value);          //will only be processed if Sale present and found 

                    int ConsignmentId = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ConsignmentId")].Value); //id field AUConsignmentID
                    string MaterialType = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MaterialType")].Value);
                    string Country1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Country1")].Value);
                    string StateProvince1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "StateProvince1")].Value);
                    string Country2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Country2")].Value);
                    string StateProvince2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "StateProvince2")].Value);
                    string Country3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Country3")].Value);
                    string StateProvince3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "StateProvince3")].Value);
                    string Format = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Format")].Value);
                    string FormFactor = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "FormFactor")].Value);
                    string UsageState = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "UsageState")].Value);
                    string Grade = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "Grade")].Value);
                    decimal ReserveAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReserveAmt")].Value);
                    decimal EstimatedLowBidAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "EstimatedLowBidAmt")].Value);
                    decimal EstimatedHighBidAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "EstimatedHighBidAmt")].Value);
                    decimal EstimatedRealizationAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "EstimatedRealizationAmt")].Value);
                    decimal OpeningBidAmt = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "OpeningBidAmt")].Value);
                    string SymbolSummary = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "SymbolSummary")].Value);
                    int ReferenceId1 = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceId1")].Value); //id field AUConsignmentID
                    string ReferenceNbr1 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceNbr1")].Value);
                    decimal ReferenceValue1 = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceValue1")].Value);
                    int ReferenceId2 = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceId2")].Value); //id field AUConsignmentID
                    string ReferenceNbr2 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceNbr2")].Value);
                    decimal ReferenceValue2 = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceValue2")].Value);
                    int ReferenceId3 = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceId3")].Value); //id field AUConsignmentID
                    string ReferenceNbr3 = ConvertColumnToString(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceNbr3")].Value);
                    decimal ReferenceValue3 = Convert.ToDecimal(worksheet.Cells[iRow, GetColumnIndex(properties, "ReferenceValue3")].Value);

                    #region Initial AUConsignor Edits

                    //It's ok if sale is not provided, but if it is provided it must already exist in the system and can't create a 1-lot-2-active-sale situation
                    sl = null; //init existing matching junction from previous record
                    csale = null;
                    var currTime = System.DateTime.UtcNow;

                    if (!String.IsNullOrEmpty(SaleNbr))
                    {
                        //ensure that the AUSale exists before you store any product/lot
                        csale = _consignorService.GetSaleByNbr(SaleNbr);
                        if (csale == null)
                        {
                            logError = string.Format("Lot Import: Ignoring row because Sale can not be found row# {0}/ Sale# {1}. Please store the sale in the system and re-import the row", iRow, SaleNbr);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                        else
                        {
                            if (csale.SaleIsAwarded)
                            {

                                logError = string.Format("Lot Import: This lot row#{0} can not be moved to a sale id/nbr: {1}/{2} that is awarded already.", iRow,  csale.AUSaleID, csale.AUSaleNbr);
                                _logger.InsertLog(LogLevel.Error, logError);
                                totalerrors++;
                            }
                            else
                            {
                                var l = _consignorService.GetLotBySku(sku);
                                if (l != null)
                                {
                                    if (l.IsLotSold)        //import changes can't be made to sold lots
                                    {
                                        logError = string.Format("Lot Import: This lot row#{0} is already sold in sale number {1}. Import changes can not be made to sold lots - row ignored", iRow, l.LotAwardSaleNbr);
                                        _logger.InsertLog(LogLevel.Error, logError);
                                        totalerrors++;
                                    }
                                    else 
                                    {   
                                       // get the one ausalelot record that is not history:
                                        var s = l.AUSaleRecords.FirstOrDefault(x => !x.IsHistory);
                                        if (s != null)
                                        {
                                            if (s.AUSaleID != csale.AUSaleID)  //trying to import to a different sale
                                            {
                                                //see if moving FROM an active or future sale and the lot has bids - No good
                                                //if (s.SaleIsActive && csale.SaleIsActive)    //imported lot can not be in 2 active sales. TODO: take sale is active out of junction
                                                if  ((!l.IsLotSold && s.SaleStartDateTime < currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0) ||
                                                    (!l.IsLotSold && s.SaleStartDateTime > currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0)
                                                    )
                                                {
                                                    logError = string.Format("Lot Import: This lot row#{0} already exists in active or future sale id {1} and has bids. A lot can not be moved if it has active bids. You should remove the bids in saleId {1} manually and then re-import to move this lot to the new sale id {2}.", iRow, s.AUSaleID, csale.AUSaleID);
                                                    _logger.InsertLog(LogLevel.Error, logError);
                                                    totalerrors++;
                                                }
                                                else if (csale.SaleIsAwarded)
                                                {

                                                    logError = string.Format("Lot Import: This lot row#{0} can not be moved to a sale id/nbr: {1}/{2} that is awarded already.", iRow,  csale.AUSaleID, csale.AUSaleNbr);
                                                    _logger.InsertLog(LogLevel.Error, logError);
                                                    totalerrors++;
                                                }
                                                else
                                                {
                                                    sl = s;     //capture the existing junction for this sale-lot (already exists in another sale but ok to move). It will be used when sale-lot junction to be created/updated
                                                }
                                            }
                                            else //(s.AUSaleID == csale.AUSaleID - lot already exixts in this sale so save the junction record
                                            {
                                                sl = s;     //capture the existing junction for this sale-lot (already exists in import sale). It will be used when sale-lot junction to be created/updated
                                            }
                                        } //leave sl null if existing "non-history" salelot junction not found - indicates lot exists but is not in any sale. Since you've already found the import sale you're good to go
                                    }
                                }  //lot specified in import does not exist and sale specified is not awarded yet, so ok to import. sl is null because not associated yet
                            }
                        }
                    }
                    else    //salenbr not specified in import - see if need to remove and if ok
                    {
                        var l = _consignorService.GetLotBySku(sku);
                        if (l != null)
                        {
                            if (l.IsLotSold)        //import changes can't be made to sold lots
                            {
                                logError = string.Format("Lot Import: This lot row#{0} is already sold in sale number {1}. Import changes can not be made to sold lots - row ignored", iRow, l.LotAwardSaleNbr);
                                _logger.InsertLog(LogLevel.Error, logError);
                                totalerrors++;
                            }
                            else
                            {
                                //need to check if lot is associated to an awarded sale or active/future sale with bids before you remove it
                                var s = l.AUSaleRecords.FirstOrDefault(x => !x.IsHistory);
                                if (s != null)
                                {
                                    if ((!l.IsLotSold && s.SaleStartDateTime < currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0) ||
                                         (!l.IsLotSold && s.SaleStartDateTime > currTime && s.SaleEndDateTime > currTime && l.LastBidAmt != 0)
                                        )
                                    {
                                        logError = string.Format("Lot Import: This lot row#{0} already exists in active or future sale id {1} and has bids. A lot can not be removed from sale as specified in the import. You should remove the bids in saleId {1} manually and then re-import to remove this lot.", iRow, s.AUSaleID);
                                        _logger.InsertLog(LogLevel.Error, logError);
                                        totalerrors++;
                                    }
                                    else if (s.SaleIsAwarded)
                                    {

                                        logError = string.Format("Lot Import: This lot row#{0} can not be removed from a sale id/nbr: {1} that is awarded already.", iRow, s.AUSaleID, s.AUSaleRecord.AUSaleNbr);
                                        _logger.InsertLog(LogLevel.Error, logError);
                                        totalerrors++;
                                    }
                                    else
                                    {
                                        sl = s;     //capture the existing junction for this sale-lot (already exists in another sale but ok to move). It will be used when sale-lot junction to be created/updated
                                    }
                                } //ale number not provided in import lot exists but not associated to a sale so let it fly
                            }
                        }   //sale number not provided in import, and lot does not exist yet so let it fly
                    }
                

                    //Consignment must be present and exist in the system
                    if (ConsignmentId != 0)
                    {
                        //ensure that the AUSale exists
                        var cconsignment = _consignorService.GetConsignmentById(ConsignmentId);
                        if (cconsignment == null)
                        {
                            logError = string.Format("Lot Import: Ignoring row because Consignment ID can not be found in system row# {0}/ ConsignmentId# {1}. Please store the consignment in the system and re-import the row", iRow, ConsignmentId);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                    }
                    else
                    {
                        logError = string.Format("Lot Import: Consignment required - ignoring row because Consignment ID was not provided for row# {0}. Please record the consignment id and re-import the row", iRow);
                        _logger.InsertLog(LogLevel.Error, logError);
                        totalerrors++;
                        //break;
                    }

                    //Country1 must be present and exist in the system
                    if (!String.IsNullOrEmpty(Country1))
                    {
                        var ccountry1 = _consignorService.GetCountryByName(Country1.Trim());
                        if (ccountry1 == null)
                        {
                            logError = string.Format("Lot Import: Ignoring row because Country1 can not be found in system for row# {0}/ Country1: {1}. Please record the country and re-import the row", iRow, Country1);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                        else        //It's ok if StateProvince1 is not provided, but if it is provided it must already exist in the system for the Country1
                            if (!String.IsNullOrEmpty(StateProvince1))
                            {
                                //ensure that the StateProvince1 exists if it's given
                                var cstateprovince1 = _consignorService.GetStateProvinceByName(StateProvince1.Trim(), ccountry1.Id);
                                if (cstateprovince1 == null)
                                {
                                    logError = string.Format("Lot Import: Ignoring row because StateProvince1 not found in system for row# {0}/ Country1: {1} / StateProvince1 {2}. Please store the StateProvince1 in the system and record on row and re-import", iRow, Country1, StateProvince1);
                                    _logger.InsertLog(LogLevel.Error, logError);
                                    totalerrors++;
                                    //break;
                                }
                            }
                    }
                    else
                    {
                        logError = string.Format("Lot Import: Country1 required - Ignoring row because Country1 not present on row# {0}. Please store the country and record on row and re-import", iRow);
                        _logger.InsertLog(LogLevel.Error, logError);
                        totalerrors++;

                       if (!String.IsNullOrEmpty(StateProvince1))
                        {
                            logError = string.Format("Lot Import: Ignoring row because StateProvince1 provided for row with no Country1 row# {0}/ StateProvince1: {1}. Please provide Country1 and StateProvince1 on row and re-import", iRow, StateProvince1);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                        }
                       //break;
                    }

                    //Its ok if Country2 not present but if it is it must exist in the system
                    //if (!String.IsNullOrEmpty(Country2.Trim()))
                    if (!String.IsNullOrEmpty(Country2))
                    {
                        var ccountry2 = _consignorService.GetCountryByName(Country2.Trim());
                        if (ccountry2 == null)
                        {
                            logError = string.Format("Lot Import: Ignoring row because Country2 can not be found in system for row# {0}/ Country2: {1}. Please record the country in the system and re-import the row", iRow, Country2);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                        else        //It's ok if StateProvince2 is not provided, but if it is provided it must already exist in the system for Country2
                            if (!String.IsNullOrEmpty(StateProvince2))
                            {
                                //ensure that the StateProvince1 exists if it's given
                                var cstateprovince2 = _consignorService.GetStateProvinceByName(StateProvince2.Trim(), ccountry2.Id);
                                if (cstateprovince2 == null)
                                {
                                    logError = string.Format("Lot Import: Ignoring row because StateProvince2 not found in system for row# {0}/ Country2: {1} / StateProvince2 {2}. Please store the StateProvince3 in the system and record on row and re-import", iRow, Country2, StateProvince2);
                                    _logger.InsertLog(LogLevel.Error, logError);
                                    totalerrors++;
                                    //break;
                                }
                            }
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(StateProvince2))
                        {
                            logError = string.Format("Lot Import: Ignoring row because StateProvince2 provided for row with no Country2 row# {0}/ StateProvince2: {1}. Please provide Country2 and StateProvince2 on row and re-import", iRow, StateProvince2);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                    }


                    //Its ok if Country3 not present but if it is it must exist in the system
                    if (!String.IsNullOrEmpty(Country3))
                    {
                        var ccountry3 = _consignorService.GetCountryByName(Country2.Trim());
                        if (ccountry3 == null)
                        {
                            logError = string.Format("Lot Import: Ignoring row because Country3 can not be found in system for row# {0}/ Country3: {1}. Please record the country in the system and re-import the row", iRow, Country3);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                        else        //It's ok if StateProvince2 is not provided, but if it is provided it must already exist in the system for Country2
                            if (!String.IsNullOrEmpty(StateProvince3))
                            {
                                //ensure that the StateProvince1 exists if it's given
                                var cstateprovince3 = _consignorService.GetStateProvinceByName(StateProvince3.Trim(), ccountry3.Id);
                                if (cstateprovince3 == null)
                                {
                                    logError = string.Format("Lot Import: Ignoring row because StateProvince3 not found in system for row# {0}/ Country3: {1} / StateProvince3 {2}. Please store the StateProvince3 in the system and record on row and re-import", iRow, Country3, StateProvince3);
                                    _logger.InsertLog(LogLevel.Error, logError);
                                    totalerrors++;
                                    //break;
                                }
                            }
                    }
                    else
                    {
                        if (!String.IsNullOrEmpty(StateProvince3))
                        {
                            logError = string.Format("Lot Import: Ignoring row because StateProvince3 provided for row with no Country3 row# {0}/ StateProvince3: {1}. Please provide Country3 and StateProvince3 on row and re-import", iRow, StateProvince3);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                    }


                    //_logger.InsertLog(LogLevel.Information, "Import finished loading data record:" + iRow.ToString() + "now get product by sku");

                    var product = new Product();
                    var lot = new AULotRecord();

                    if (String.IsNullOrEmpty(sku))
                    {
                        logError = string.Format("Lot Import: Ignoring row because Product SKU not provided for row#{0}. Please update with the correct SKU and re-import", iRow);
                        _logger.InsertLog(LogLevel.Error, logError);
                        totalerrors++;
                        //break;
                    }
                    else
                    {
                        //TODO: SKU is mandatory - document in spreadsheet
                        product = _productService.GetProductBySku(sku);
                        lot = _consignorService.GetLotBySku(sku);

                        //Log as error when product already exists but lot doesn't and vica versa. This indicates one somehow got deleted when the other didn't. This is also logged in audit
                        //if (product != null && lot == null)  //don't do this check because it may be a brend new product
                        //{

                        //    logError = string.Format("Lot Import: Ignoring row because Product SKU was found but Lot SKU was not for row#{0}/SKU:{1}. Please notify the System Admin to research", iRow, sku);
                        //    _logger.InsertLog(LogLevel.Error, logError);
                        //    totalerrors++;
                        //    //break;
                        //}
                        //else if (product == null && lot != null)
                        if (product == null && lot != null)
                        {
                            logError = string.Format("Lot Import: Ignoring row because Lot SKU was found but Product SKU was not for row#{0}/SKU:{1}. Please notify the System Admin to research", iRow, sku);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                        else if (product != null && lot == null)
                        {
                            logError = string.Format("Lot Import: Ignoring row because Lot SKU was not found but Product SKU was found for row#{0}/SKU:{1}. Please notify the System Admin to research", iRow, sku);
                            _logger.InsertLog(LogLevel.Error, logError);
                            totalerrors++;
                            //break;
                        }
                    }
                    //TODO: If Error consignment id provided in file for a combined lot or a split lot

                    //if any errors from above edit-controls, stop this row and go to next. Count this record as ignored.
                    if (totalerrors > 0)
                    {
                        recordsignored++;

                        //next product
                        iRow++;
                        continue;   //skip record and go to next iteration 
                    }

                    #endregion 


                    bool newProduct = false;
                    if (product == null)
                    {
                        product = new Product();
                        lot = new AULotRecord();  //NJM
                        newProduct = true;
                    }
                    product.ProductTypeId = productTypeId;
                    product.ParentGroupedProductId = parentGroupedProductId;
                    product.VisibleIndividually = visibleIndividually;
                    product.Name = name;
                    product.ShortDescription = shortDescription;
                    product.FullDescription = fullDescription;
                    product.VendorId = vendorId;
                    product.ProductTemplateId = productTemplateId;
                    product.ShowOnHomePage = showOnHomePage;
                    product.MetaKeywords = metaKeywords;
                    product.MetaDescription = metaDescription;
                    product.MetaTitle = metaTitle;
                    product.AllowCustomerReviews = allowCustomerReviews;
                    product.Sku = sku;
                    product.ManufacturerPartNumber = manufacturerPartNumber;
                    product.Gtin = gtin;
                    product.IsGiftCard = isGiftCard;
                    product.GiftCardTypeId = giftCardTypeId;
                    product.RequireOtherProducts = requireOtherProducts;
                    product.RequiredProductIds = requiredProductIds;
                    product.AutomaticallyAddRequiredProducts = automaticallyAddRequiredProducts;
                    product.IsDownload = isDownload;
                    product.DownloadId = downloadId;
                    product.UnlimitedDownloads = unlimitedDownloads;
                    product.MaxNumberOfDownloads = maxNumberOfDownloads;
                    product.DownloadActivationTypeId = downloadActivationTypeId;
                    product.HasSampleDownload = hasSampleDownload;
                    product.SampleDownloadId = sampleDownloadId;
                    product.HasUserAgreement = hasUserAgreement;
                    product.UserAgreementText = userAgreementText;
                    product.IsRecurring = isRecurring;
                    product.RecurringCycleLength = recurringCycleLength;
                    product.RecurringCyclePeriodId = recurringCyclePeriodId;
                    product.RecurringTotalCycles = recurringTotalCycles;
                    product.IsRental = isRental;
                    product.RentalPriceLength = rentalPriceLength;
                    product.RentalPricePeriodId = rentalPricePeriodId;
                    product.IsShipEnabled = isShipEnabled;
                    product.IsFreeShipping = isFreeShipping;
                    product.ShipSeparately = shipSeparately;
                    product.AdditionalShippingCharge = additionalShippingCharge;
                    product.DeliveryDateId = deliveryDateId;
                    product.IsTaxExempt = isTaxExempt;
                    product.TaxCategoryId = taxCategoryId;
                    product.IsTelecommunicationsOrBroadcastingOrElectronicServices = isTelecommunicationsOrBroadcastingOrElectronicServices;
                    product.ManageInventoryMethodId = manageInventoryMethodId;
                    product.UseMultipleWarehouses = useMultipleWarehouses;
                    product.WarehouseId = warehouseId;
                    product.StockQuantity = stockQuantity;
                    product.DisplayStockAvailability = displayStockAvailability;
                    product.DisplayStockQuantity = displayStockQuantity;
                    product.MinStockQuantity = minStockQuantity;
                    product.LowStockActivityId = lowStockActivityId;
                    product.NotifyAdminForQuantityBelow = notifyAdminForQuantityBelow;
                    product.BackorderModeId = backorderModeId;
                    product.AllowBackInStockSubscriptions = allowBackInStockSubscriptions;
                    product.OrderMinimumQuantity = orderMinimumQuantity;
                    product.OrderMaximumQuantity = orderMaximumQuantity;
                    product.AllowedQuantities = allowedQuantities;
                    product.AllowAddingOnlyExistingAttributeCombinations = allowAddingOnlyExistingAttributeCombinations;
                    product.DisableBuyButton = disableBuyButton;
                    product.DisableWishlistButton = disableWishlistButton;
                    product.AvailableForPreOrder = availableForPreOrder;
                    product.PreOrderAvailabilityStartDateTimeUtc = preOrderAvailabilityStartDateTimeUtc;
                    product.CallForPrice = callForPrice;
                    product.Price = price;
                    product.OldPrice = oldPrice;
                    product.ProductCost = productCost;
                    product.SpecialPrice = specialPrice;
                    product.SpecialPriceStartDateTimeUtc = specialPriceStartDateTimeUtc;
                    product.SpecialPriceEndDateTimeUtc = specialPriceEndDateTimeUtc;
                    product.CustomerEntersPrice = customerEntersPrice;
                    product.MinimumCustomerEnteredPrice = minimumCustomerEnteredPrice;
                    product.MaximumCustomerEnteredPrice = maximumCustomerEnteredPrice;
                    product.Weight = weight;
                    product.Length = length;
                    product.Width = width;
                    product.Height = height;
                    product.Published = published;
                    product.CreatedOnUtc = createdOnUtc;
                    product.UpdatedOnUtc = DateTime.UtcNow;

                    //_logger.InsertLog(LogLevel.Information, "Import finished loading product info:" + iRow.ToString() + "now load lot info");


                    //NJM: start philatelic shit
                    lot.Sku = sku;   //this is sequence nbr
                    lot.EstimatedLowBidAmt = EstimatedLowBidAmt;
                    lot.EstimatedHighBidAmt = EstimatedHighBidAmt;
                    lot.EstimatedRealizationAmt = EstimatedRealizationAmt;
                    lot.OpeningBidAmt = OpeningBidAmt;
                    lot.ReserveAmt = ReserveAmt;


                    //TODO: Need to make this transactional
                    //TODO - need additionalShippingCharge try catch here that lets the rest of the import worksheet and hasSampleDownload a good message - also need to undo whatever done. Need ability to rerun import for failed records 
                    if (newProduct)
                    {
                        lot.CreatedByUsername = CurrentCustomer.Username;
                        lot.UpdatedByUsername = CurrentCustomer.Username;
                        lot.CreatedOnDateTime = System.DateTime.UtcNow;
                        lot.UpdatedOnDateTime = lot.CreatedOnDateTime;
                        lot.AUProductID = product.Id;   //very important to set the lot product id to match the product!!!
                        lot.IsLotSold = false;


                        //keep as little time between operations as possible

                       // _logger.InsertLog(LogLevel.Information, "Inserting product:" + iRow.ToString());
                        _productService.InsertProduct(product);

                        // _logger.InsertLog(LogLevel.Information, "Inserting lot:" + iRow.ToString());

                        lot.AUProductID = product.Id;
                        //TODO: Make AULotRecord a view model so not constantly stuffing consignmentid
                        lot.ConsignmentID = ConsignmentId;
                        lot.LastBidAmt = 0;     //do this to ensure nulls don't sneak in as used to determine if bids entered

                        lot.BidPostDateTime = new DateTime(1970, 01, 01);

                        _consignorService.InsertLot(lot);
                    }
                    else
                    {  
                        lot.UpdatedByUsername = CurrentCustomer.Username;
                        lot.UpdatedOnDateTime = System.DateTime.UtcNow;

                        //keep as little time between operations as possible
                        _productService.UpdateProduct(product);

                        //TODO: Make AULotRecord a view model so not constantly stuffing consignmentid
                        lot.ConsignmentID = ConsignmentId;
                        _consignorService.UpdateLot(lot);
                    }

                    //search engine name
                    _urlRecordService.SaveSlug(product, product.ValidateSeName(seName, product.Name, true), 0);

                    //category mappings
                    if (!String.IsNullOrEmpty(categoryIds))
                    {
                        foreach (var id in categoryIds.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x.Trim())))
                        {
                            if (product.ProductCategories.FirstOrDefault(x => x.CategoryId == id) == null)
                            {
                                //ensure that category exists
                                var category = _categoryService.GetCategoryById(id);
                                if (category != null)
                                {
                                    var productCategory = new ProductCategory
                                    {
                                        ProductId = product.Id,
                                        CategoryId = category.Id,
                                        IsFeaturedProduct = false,
                                        DisplayOrder = 1
                                    };
                                    _categoryService.InsertProductCategory(productCategory);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: Category ID not found but storing lot anyway row#{0}/Id:{1}.", iRow, id);
                                    _logger.InsertLog(LogLevel.Information, logError);
                                }
                            }
                        }
                    }

                    //manufacturer mappings
                    if (!String.IsNullOrEmpty(manufacturerIds))
                    {
                        foreach (var id in manufacturerIds.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries).Select(x => Convert.ToInt32(x.Trim())))
                        {
                            if (product.ProductManufacturers.FirstOrDefault(x => x.ManufacturerId == id) == null)
                            {
                                //ensure that manufacturer exists
                                var manufacturer = _manufacturerService.GetManufacturerById(id);
                                if (manufacturer != null)
                                {
                                    var productManufacturer = new ProductManufacturer
                                    {
                                        ProductId = product.Id,
                                        ManufacturerId = manufacturer.Id,
                                        IsFeaturedProduct = false,
                                        DisplayOrder = 1
                                    };
                                    _manufacturerService.InsertProductManufacturer(productManufacturer);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: Manufacturer ID not found but storing lot anyway row#{0}/Id:{1}.", iRow, id);
                                    _logger.InsertLog(LogLevel.Information, logError);
                                }
                            }
                        }
                    }

                    //pictures
                    foreach (var picturePath in new[] { picture1, picture2, picture3 })
                    {
                        if (String.IsNullOrEmpty(picturePath))
                            continue;

                        var mimeType = GetMimeTypeFromFilePath(picturePath);
                        var newPictureBinary = File.ReadAllBytes(picturePath);
                        var pictureAlreadyExists = false;
                        if (!newProduct)
                        {
                            //compare with existing product pictures
                            var existingPictures = _pictureService.GetPicturesByProductId(product.Id);
                            foreach (var existingPicture in existingPictures)
                            {
                                var existingBinary = _pictureService.LoadPictureBinary(existingPicture);
                                //picture binary after validation (like in database)
                                var validatedPictureBinary = _pictureService.ValidatePicture(newPictureBinary, mimeType);
                                if (existingBinary.SequenceEqual(validatedPictureBinary))
                                {
                                    //the same picture content
                                    pictureAlreadyExists = true;
                                    break;
                                }
                            }
                        }


                        //OLD SIGNATURE FOR _pictureService.GetPictureSeName
                        //if (!pictureAlreadyExists)
                        //{
                        //    product.ProductPictures.Add(new ProductPicture
                        //    {
                        //        Picture = _pictureService.InsertPicture(newPictureBinary, mimeType, _pictureService.GetPictureSeName(name), true),
                        //        DisplayOrder = 1,
                        //    });
                        //    _productService.UpdateProduct(product);
                        //}


                        if (!pictureAlreadyExists)
                        {
                            product.ProductPictures.Add(new ProductPicture
                            {
                                Picture = _pictureService.InsertPicture(newPictureBinary, mimeType, _pictureService.GetPictureSeName(name)),
                                DisplayOrder = 1,
                            });
                            _productService.UpdateProduct(product);
                        }


                    }

                    //update "HasTierPrices" and "HasDiscountsApplied" properties
                    //NJM for performance - I think thiswill always default to 0 because there's nothing in the excel file
                    _productService.UpdateHasTierPricesProperty(product);
                    _productService.UpdateHasDiscountsApplied(product);

                    //*********************************************************************************************************************************
                    //NJM: AUConsignor begin philatelic lot relationships
                    //********************************************************************************************************************************

                    //TODO: REFACTOR SALE-LOT THIS SO CAN CALL FROMMANAGESALESPOPUP AS WELL
                    //For sale it looks like it ignores it if it can't find it. Need to implement auditlog of all imports
                    if (!String.IsNullOrEmpty(SaleNbr))
                    {
                        //ensure that the AUSale exists
                        //AUSaleLotRecord s1 = null;
                        //var sale = _consignorService.GetSaleByNbr(SaleNbr);
                        //TODO: PUT FOREIGN KEY CONSTRAINTS ON AUSALELOT
                        if (csale != null)  //sale specified on import already retrived in overall edits above. If it is null you have other problems
                        {                         
                            if (sl == null)  //no sale junction exists for this lot as determined in overall edits above. Just create a new salelot junction.
                                {
                                    var saleLot = new AUSaleLotRecord
                                    {
                                        AUSaleID = csale.AUSaleID,
                                        AULotID = lot.AULotID,
                                        LotNbr = LotNbr,                        //load up LotNbr from the spreadsheet
                                        Sku = sku,
                                        SaleStartDateTime = csale.SaleStartDateTime,
                                        SaleEndDateTime = csale.SaleEndDateTime,
                                        IsHistory = false,
                                        SaleIsAwarded  = csale.SaleIsAwarded,
                                        SaleIsActive = csale.SaleIsActive,       //pull this indicator from the Sale record - don't load from spreadsheet
                                        DisplayOrder = DisplayOrder             //load up DisplayOrder from the spreadsheet
                                    };

                                    _consignorService.InsertSaleLot(saleLot);
                                }
                            else    //Sale exists and the junction AUSaleLot record does as well. If juntion is to import sale (already exixts in sale), just update it with fresh sale info
                                    //If junction is to a different sale you'll need to move to the new (import sale)
                                {
                                    if (SaleNbr == sl.AUSaleRecord.AUSaleNbr)  //import sale number is the same as the currently assigned sale number
                                    {
                                        sl.AUSaleID = csale.AUSaleID;
                                        sl.AULotID = lot.AULotID;
                                        sl.LotNbr = LotNbr;
                                        sl.Sku = sku;
                                        sl.SaleStartDateTime = csale.SaleStartDateTime;
                                        sl.SaleEndDateTime = csale.SaleEndDateTime;
                                        //sl.IsHistory = false;     //don't touch this as whatever was there should remain there. Note: sl is only populated with the non-history junction for the lot
                                        sl.SaleIsAwarded = csale.SaleIsAwarded;
                                        sl.SaleIsActive = csale.SaleIsActive;
                                        sl.DisplayOrder = DisplayOrder;
                                        _consignorService.UpdateSaleLot(sl);
                                    }
                                    else     //existing "non-history" salelot is to different sale than import sale - "MOVE" the sale lot
                                    {
                                        AUSaleLotRecord newSaleLot = new AUSaleLotRecord()
                                        {
                                            AUSaleID = csale.AUSaleID,
                                            LotNbr = LotNbr,                        //load up LotNbr from the spreadsheet
                                            SaleIsActive = csale.SaleIsActive,       //pull this indicator from the Sale record - don't load from spreadsheet
                                            DisplayOrder = DisplayOrder,
                                            SaleStartDateTime = csale.SaleStartDateTime,
                                            SaleEndDateTime = csale.SaleEndDateTime,
                                            SaleIsAwarded = csale.SaleIsAwarded,
                                            IsHistory = false,
                                            AUProductId = lot.AUProductID,
                                            Sku = lot.Sku
                                        };

                                        _consignorService.MoveSaleLot(sl, newSaleLot);
                                    }
                                }
                        }   //SaleNbr not found (csale == null) -- this will have been logged as an error in top edits

                    }
                    else   //import sale number is null - need to see if need to remove lot
                    {
                        if (sl != null) //no import sale but lot exists in a sale, so remove it from the sale
                        {
                            _consignorService.DeleteSaleLotByLotId(lot.AULotID, sl.AUSaleID);
                        }
                    }


                        //%%%%%%%%%%%%%%need logic if in sale and removing sale


                    //NOTE: The above edits will stop process for this record if Consignment ID not found in system
                    //Consignment is required for an original lot. An original lot can only belong to one consignment. A combined lot can not belong to a consignment
                    //directly - it only belongs to consignments through it's relationship to the orginal lot.
                    if (ConsignmentId != 0)
                    {
                        //ensure that the AUSale exists
                        var consignment = _consignorService.GetConsignmentById(ConsignmentId);
                        if (consignment != null)
                        {
                            //TODO: mutiple imports that change the consignment between imports for a lot will not get rid of the old relationship
                            //if (lot.AUSaleRecords.FirstOrDefault(x => x.AUSaleID == sale.AUSaleID) == null) //%%%%Value cn not be null?????
                            
                            //see if this lot was already associated to another consignment
                            var lc = lot.AUConsignmentRecords.FirstOrDefault(x => x.AUConsignmentID != ConsignmentId);
                            //if it was associated to a different consignment, you'll change the consignment assignment but warn them as well
                            if (lc != null)
                            {
                                logError = string.Format("Lot Import: This lot belonged to a different consignment than in the import - row#{0} belonged to consignmentId:{1} but was changed to consignmentId {2}. You should review the lot fees (if any) or else fees will be inherited from the new consignment.", iRow, lc.AUConsignmentID, consignment.AUConsignmentID);
                                _logger.InsertLog(LogLevel.Warning, logError);

                                int origConsignmentId = lc.AUConsignmentID;
                                lc.AUConsignmentID = consignment.Id;
                                _consignorService.UpdateConsignmentLot(lc, origConsignmentId);
                            }
                            else //lot was not already assigned to a different consignment
                            {
                                var cl = consignment.AULotRecords.FirstOrDefault(x => x.AULotID == lot.AULotID);
                                if (cl == null) //Consignment exists but the junction AUConsignmentLot record doesn't
                                {
                                    var consignmentLot = new AUConsignmentLotRecord
                                    {
                                        AUConsignmentID = consignment.AUConsignmentID,
                                        AULotID = lot.AULotID,
                                        AULotRecord = lot,
                                        AUConsignmentRecord = consignment,

                                    };
                                    _consignorService.InsertConsignmentLot(consignmentLot);
                                }
                            }
                        }  //TODO: add abort here (and at Sale) because edit already determined consignment exists so something bad happened
                    }

                    //Junction Lot to Country1 (AUCountryLot)
                    if (!String.IsNullOrEmpty(Country1))
                    {
                        //ensure that the country1 exists, because you will add its relationship to lot as well as a specification attribute
                        var country = _consignorService.GetCountryByName(Country1.Trim());
                        if (country != null)
                        {
                           
                            var query = from cl in _countrylotRepo.Table
                                       where cl.AULotID == lot.AULotID && cl.AUCountryID == country.Id
                                       select cl;
                            var clot = query.FirstOrDefault();
                            if (clot == null) //Country exists exists but the junction AUCountryLot record doesn't
                            {
                                var countryLot = new AUCountryLotRecord
                                {
                                    AULotID = lot.AULotID,
                                    AUCountryID = country.Id,
                                };
                                _consignorService.InsertCountryLot(countryLot);
                            }
                        }

                        //Junction Lot to StateProvince1 (AUStateProvinceLot) - can only happen if you have Country1
                        if (!String.IsNullOrEmpty(StateProvince1))
                        {
                            //ensure that the AUSale exists
                            var stateprovince = _consignorService.GetStateProvinceByName(StateProvince1.Trim(), country.Id);
                            if (stateprovince != null)
                            {

                                var query = from spl in _stateprovincelotRepo.Table
                                            where spl.AULotID == lot.AULotID && spl.AUStateProvinceID == stateprovince.Id
                                            select spl;
                                var splot = query.FirstOrDefault();
                                if (splot == null) //StateProvince exists exists but the junction AUStateProvinceLot record doesn't
                                {
                                    var stateprovinceLot = new AUStateProvinceLotRecord
                                    {
                                        AULotID = lot.AULotID,
                                        AUStateProvinceID = stateprovince.Id,
                                    };
                                    _consignorService.InsertStateProvinceLot(stateprovinceLot);
                                }
                            }

                        }
                    } //end Country1

                    //Junction Lot to Country2 (AUCountryLot)
                    if (!String.IsNullOrEmpty(Country2))
                    {
                        //ensure that the AUSale exists
                        var country = _consignorService.GetCountryByName(Country2.Trim());
                        if (country != null)
                        {

                            var query = from cl in _countrylotRepo.Table
                                        where cl.AULotID == lot.AULotID && cl.AUCountryID == country.Id
                                        select cl;
                            var clot = query.FirstOrDefault();
                            if (clot == null) //Country exists exists but the junction AUCountryLot record doesn't
                            {
                                var countryLot = new AUCountryLotRecord
                                {
                                    AULotID = lot.AULotID,
                                    AUCountryID = country.Id,
                                };
                                _consignorService.InsertCountryLot(countryLot);
                            }
                        }

                        //Junction Lot to StateProvince (AUStateProvinceLot) - can only happen if you have Country1
                        if (!String.IsNullOrEmpty(StateProvince2))
                        {
                            //ensure that the AUSale exists
                            var stateprovince = _consignorService.GetStateProvinceByName(StateProvince2.Trim(), country.Id);
                            if (stateprovince != null)
                            {

                                var query = from spl in _stateprovincelotRepo.Table
                                            where spl.AULotID == lot.AULotID && spl.AUStateProvinceID == stateprovince.Id
                                            select spl;
                                var splot = query.FirstOrDefault();
                                if (splot == null) //StateProvince exists exists but the junction AUStateProvinceLot record doesn't
                                {
                                    var stateprovinceLot = new AUStateProvinceLotRecord
                                    {
                                        AULotID = lot.AULotID,
                                        AUStateProvinceID = stateprovince.Id,
                                    };
                                    _consignorService.InsertStateProvinceLot(stateprovinceLot);
                                }
                            }

                        }
                    } //end Country2



                    //Junction Lot to Country3 (AUCountryLot)
                    if (!String.IsNullOrEmpty(Country3))
                    {
                        //ensure that the AUSale exists
                        var country = _consignorService.GetCountryByName(Country3.Trim());
                        if (country != null)
                        {

                            var query = from cl in _countrylotRepo.Table
                                        where cl.AULotID == lot.AULotID && cl.AUCountryID == country.Id
                                        select cl;
                            var clot = query.FirstOrDefault();
                            if (clot == null) //Country exists exists but the junction AUCountryLot record doesn't
                            {
                                var countryLot = new AUCountryLotRecord
                                {
                                    AULotID = lot.AULotID,
                                    AUCountryID = country.Id,
                                };
                                _consignorService.InsertCountryLot(countryLot);
                            }
                        }

                        //Junction Lot to StateProvince1 (AUStateProvinceLot) - can only happen if you have Country1
                        if (!String.IsNullOrEmpty(StateProvince3))
                        {
                            //ensure that the AUSale exists
                            var stateprovince = _consignorService.GetStateProvinceByName(StateProvince3.Trim(), country.Id);
                            if (stateprovince != null)
                            {

                                var query = from spl in _stateprovincelotRepo.Table
                                            where spl.AULotID == lot.AULotID && spl.AUStateProvinceID == stateprovince.Id
                                            select spl;
                                var splot = query.FirstOrDefault();
                                if (splot == null) //StateProvince exists exists but the junction AUStateProvinceLot record doesn't
                                {
                                    var stateprovinceLot = new AUStateProvinceLotRecord
                                    {
                                        AULotID = lot.AULotID,
                                        AUStateProvinceID = stateprovince.Id,
                                    };
                                    _consignorService.InsertStateProvinceLot(stateprovinceLot);
                                }
                            }

                        }
                    } //end Country3

                    //Start of product specification attributes
                    //specAttributes
                    //int specFormat = 0;
                    //int specFormFactor = 0;
                    //int specUsageState = 0;
                    //int specGrade = 0;

                    //SymbolSummary

                    //TODO: For all spec attributes inquire for AllowFiltering and ShowOnProductPage (use same method as display order? but need to investigate where these are stored)
                    if ((!String.IsNullOrEmpty(MaterialType)) || (!String.IsNullOrEmpty(Format)) || (!String.IsNullOrEmpty(FormFactor)) || (!String.IsNullOrEmpty(UsageState)) || (!String.IsNullOrEmpty(Grade)) || (!String.IsNullOrEmpty(SymbolSummary)))
                    {
                        var productSpecs = _specificationAttributeService.GetProductSpecificationAttributes(product.Id);    //only get the produc specs one time if any of the spec attributes is filled in


                        //FormFactor
                        if (!String.IsNullOrEmpty(MaterialType))
                        {
                            optionExists = false;
                            specExists = false;
                            if (productSpecs.Count() > 0)
                            {
                                foreach (ProductSpecificationAttribute ps in productSpecs)
                                {
                                    if (ps.SpecificationAttributeOption.SpecificationAttributeId == specMaterialType)
                                    {
                                        specExists = true;
                                        if (ps.SpecificationAttributeOption.Name == MaterialType)   //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
                                        {
                                            optionExists = true;
                                            break;
                                        }
                                    }
                                }
                            }

                            if (!optionExists)
                            {

                                var specOption = GetSpecOptionByName(specMaterialType, MaterialType); //get the option id so can create products spec attribute below
                                if (specOption.Name != null)
                                {
                                    var psa = new ProductSpecificationAttribute
                                    {
                                        //AttributeTypeId = attributeTypeId,
                                        AttributeTypeId = (int)SpecificationAttributeType.Option,
                                        SpecificationAttributeOptionId = specOption.Id,
                                        ProductId = product.Id,
                                        CustomValue = null,
                                        AllowFiltering = true,  //can do this since it's an "Option" attribute type
                                        ShowOnProductPage = true,
                                        DisplayOrder = specMaterialTypeDisplayOrder   ////Proven that this needs to be the Spec Attribute display order - not the Option display order
                                        //DisplayOrder = specOption.DisplayOrder

                                    };
                                    _specificationAttributeService.InsertProductSpecificationAttribute(psa);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/option", iRow, "Format", Format);
                                    _logger.InsertLog(LogLevel.Warning, logError);

                                }
                            }
                        }


                        
                        //Format
                        if (!String.IsNullOrEmpty(Format))
                        {
                            optionExists = false;
                            specExists = false;
                            if (productSpecs.Count() > 0)
                            {
                                foreach (ProductSpecificationAttribute ps in productSpecs)
                                {
                                    if (ps.SpecificationAttributeOption.SpecificationAttributeId == specFormat)
                                    {
                                        specExists = true;
                                        if (ps.SpecificationAttributeOption.Name == Format)   //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
                                        {
                                            optionExists = true;
                                            break;
                                        }
                                    }
                                }
                            }

                            if (!optionExists)  
                            {
                                var specOption = GetSpecOptionByName(specFormat, Format); //get the option id so can create products spec attribute below
                                if (specOption.Name != null)
                                {
                                    var psa = new ProductSpecificationAttribute
                                    {
                                        //AttributeTypeId = attributeTypeId,
                                        AttributeTypeId = (int)SpecificationAttributeType.Option,

                                        SpecificationAttributeOptionId = specOption.Id, 
                                        ProductId = product.Id,
                                        CustomValue = null,
                                        AllowFiltering = true,  //can do this since it's an "Option" attribute type
                                        ShowOnProductPage = true,
                                        DisplayOrder = specFormatDisplayOrder   ////Proven that this needs to be the Spec Attribute display order - not the Option display order
                                        //DisplayOrder = specOption.DisplayOrder
                                    };
                                    _specificationAttributeService.InsertProductSpecificationAttribute(psa);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/option", iRow, "Format", Format);
                                    _logger.InsertLog(LogLevel.Warning, logError);

                                }
                            }
                        }

                        //FormFactor
                        if (!String.IsNullOrEmpty(FormFactor))
                        {
                            optionExists = false;
                            specExists = false;
                            if (productSpecs.Count() > 0)
                            {
                                foreach (ProductSpecificationAttribute ps in productSpecs)
                                {
                                    if (ps.SpecificationAttributeOption.SpecificationAttributeId == specFormFactor)
                                    {
                                        specExists = true;
                                        if (ps.SpecificationAttributeOption.Name == FormFactor)   //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
                                        {
                                            optionExists = true;
                                            break;
                                        }
                                    }
                                }
                            }

                            if (!optionExists)
                            {

                                var specOption = GetSpecOptionByName(specFormFactor, FormFactor); //get the option id so can create products spec attribute below
                                if (specOption.Name != null)
                                {
                                    var psa = new ProductSpecificationAttribute
                                    {
                                        //AttributeTypeId = attributeTypeId,
                                        AttributeTypeId = (int)SpecificationAttributeType.Option,
                                        SpecificationAttributeOptionId = specOption.Id,
                                        ProductId = product.Id,
                                        CustomValue = null,
                                        AllowFiltering = true,  //can do this since it's an "Option" attribute type
                                        ShowOnProductPage = true,
                                        DisplayOrder = specFormFactorDisplayOrder   ////Proven that this needs to be the Spec Attribute display order - not the Option display order
                                        //DisplayOrder = specOption.DisplayOrder

                                    };
                                    _specificationAttributeService.InsertProductSpecificationAttribute(psa);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/option", iRow, "FormFactor", FormFactor);
                                    _logger.InsertLog(LogLevel.Warning, logError);

                                }
                            }
                        }


                        //UsageState
                        if (!String.IsNullOrEmpty(UsageState))
                        {
                            optionExists = false;
                            specExists = false;
                            if (productSpecs.Count() > 0)
                            {
                                foreach (ProductSpecificationAttribute ps in productSpecs)
                                {
                                    if (ps.SpecificationAttributeOption.SpecificationAttributeId == specUsageState)
                                    {
                                        specExists = true;
                                        if (ps.SpecificationAttributeOption.Name == UsageState)   //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
                                        {
                                            optionExists = true;
                                            break;
                                        }
                                    }
                                }
                            }

                            if (!optionExists)
                            {
                                var specOption = GetSpecOptionByName(specUsageState, UsageState); //get the option id so can create products spec attribute below
                                if (specOption.Name != null)
                                {
                                    var psa = new ProductSpecificationAttribute
                                    {
                                        //AttributeTypeId = attributeTypeId,
                                        AttributeTypeId = (int)SpecificationAttributeType.Option,

                                        SpecificationAttributeOptionId = specOption.Id, 
                                        ProductId = product.Id,
                                        CustomValue = null,
                                        AllowFiltering = true,  //can do this since it's an "Option" attribute type
                                        ShowOnProductPage = true,
                                        DisplayOrder = specUsageStateDisplayOrder   ////Proven that this needs to be the Spec Attribute display order - not the Option display order
                                        //DisplayOrder = specOption.DisplayOrder

                                    };
                                    _specificationAttributeService.InsertProductSpecificationAttribute(psa);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/otion", iRow, "UsageState", UsageState);
                                    _logger.InsertLog(LogLevel.Warning, logError);

                                }
                            }
                        }

                        //Grade
                        if (!String.IsNullOrEmpty(Grade))
                        {
                            optionExists = false;
                            specExists = false;
                            if (productSpecs.Count() > 0)
                            {
                                foreach (ProductSpecificationAttribute ps in productSpecs)
                                {
                                    if (ps.SpecificationAttributeOption.SpecificationAttributeId == specGrade)
                                    {
                                        specExists = true;
                                        if (ps.SpecificationAttributeOption.Name == Grade)   //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
                                        {
                                            optionExists = true;
                                            break;
                                        }
                                    }
                                }
                            }

                            if (!optionExists)
                            {
                                var specOption = GetSpecOptionByName(specGrade, Grade); //get the option id so can create products spec attribute below
                                if (specOption.Name != null)
                                {
                                    var psa = new ProductSpecificationAttribute
                                    {
                                        //AttributeTypeId = attributeTypeId,
                                        AttributeTypeId = (int)SpecificationAttributeType.Option,

                                        SpecificationAttributeOptionId = specOption.Id,
                                        ProductId = product.Id,
                                        CustomValue = null,
                                        AllowFiltering = true,  //can do this since it's an "Option" attribute type
                                        ShowOnProductPage = true,
                                        DisplayOrder = specGradeDisplayOrder   ////Proven that this needs to be the Spec Attribute display order - not the Option display order
                                        //DisplayOrder = specOption.DisplayOrder

                                    };
                                    _specificationAttributeService.InsertProductSpecificationAttribute(psa);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/otion", iRow, "Grade", Grade);
                                    _logger.InsertLog(LogLevel.Warning, logError);

                                }
                            }
                        }

                        //SymbolSummary
                        if (!String.IsNullOrEmpty(SymbolSummary))
                        {
                            optionExists = false;
                            specExists = false;
                            if (productSpecs.Count() > 0)
                            {
                                foreach (ProductSpecificationAttribute ps in productSpecs)
                                {
                                    if (ps.SpecificationAttributeOption.SpecificationAttributeId == specSymbolSummary)
                                    {
                                        specExists = true;
                                        if (ps.SpecificationAttributeOption.Name == SymbolSummary)   //Match on option name to the text in the SS. If the product spec attribute is "Format" and the option already is associated, don't create a duplicate association
                                        {
                                            optionExists = true;
                                            break;
                                        }
                                    }
                                }
                            }

                            if (!optionExists)
                            {
                                var specOption = GetSpecOptionByName(specSymbolSummary, SymbolSummary); //get the option id so can create products spec attribute below
                                if (specOption.Name != null)
                                {
                                    var psa = new ProductSpecificationAttribute
                                    {
                                        //AttributeTypeId = attributeTypeId,
                                        AttributeTypeId = (int)SpecificationAttributeType.Option,

                                        SpecificationAttributeOptionId = specOption.Id,
                                        ProductId = product.Id,
                                        CustomValue = null,
                                        AllowFiltering = true,  //can do this since it's an "Option" attribute type
                                        ShowOnProductPage = true,
                                        DisplayOrder = specSymbolSummaryDisplayOrder   ////Proven that this needs to be the Spec Attribute display order - not the Option display order
                                        //DisplayOrder = specOption.DisplayOrder

                                    };
                                    _specificationAttributeService.InsertProductSpecificationAttribute(psa);
                                }
                                else
                                {
                                    logError = string.Format("Lot Import: This Specification Attribute {1} and/or Attribute Option {2} was not found for this row#{0}. The product/lot is added but not associated to this attribute/otion", iRow, "SymbolSummary", SymbolSummary);
                                    _logger.InsertLog(LogLevel.Warning, logError);

                                }
                            }
                        }


                    }   //end of product spec attributes


                    //end of doing crap - count record as added or changed
                    recordsaddchanged++;

                    //next product
                    iRow++;
                }

                logError = string.Format("Lot Import: End Lot Import by User: {0}. #Records added or changed: {1}. #Records ignored due to errors in row: {2}", CurrentCustomer.Username, recordsaddchanged, recordsignored);
                _logger.InsertLog(LogLevel.Information, logError);

            } //end of using excel package
        } //end of class
        //TODO: copy lot provide option to copy fees and/or current active sale.
        /// <summary>
        /// Create a copy of lot associated to product. The new lot will remain within the same consignment as the old lot but
        /// we will not will not copy lot fees or any sale-lot or reference-lot junctions. The new lot will also remain within the same 
        /// country and stateprovince as the old lot. 
        /// </summary>
        /// <param name="product">The product associated to the lot to copy</param>
        /// <param name="newSKu">The new sku of product duplicate</param>
        /// <returns>The new AULotRecord copy</returns>
        /// 
        public virtual AULotRecord CopyLot(Product origProduct, string newSku)
        {
            if (origProduct == null)
                throw new ArgumentNullException("Original Product missing");

            if (String.IsNullOrEmpty(newSku))
                throw new ArgumentException("New Product sku is required");

            var newProduct = _productService.GetProductBySku(newSku);

            var origLot = _consignorService.GetLotByProductId(origProduct.Id);
            if (origLot == null)
                throw new ArgumentException("New Product sku is required");

            var currDate = System.DateTime.UtcNow;
            var currentCustomer = _authenticationService.GetAuthenticatedCustomer();

            var newLot = new AULotRecord()
            {
                AUProductID = newProduct.Id,
                Sku = newSku,
                CreatedOnDateTime = currDate,
                UpdatedOnDateTime = currDate,  //make sure dates are the same on insert
                CreatedByUsername = currentCustomer.Username,
                UpdatedByUsername = currentCustomer.Username,

                //TODO: Make this a view model - forcing this in though it doesn't hit the DB
                ConsignmentID = 999999999

            };

            //_consignorService.InsertLot(newLot);  
            //TODO: MAKE InsertLot return the new lot record so can fully subsume data layer
            //TODO: Make transaction
            _lotRepo.Insert(newLot);

            AULotLastBidChangeRecord lastbid = new AULotLastBidChangeRecord();
            lastbid.AULotId = newLot.AULotID;
            lastbid.AUProductId = newLot.AUProductID;
            lastbid.BidderId = newLot.LastBidId;
            lastbid.LastBidChangeDT = newLot.CreatedOnDateTime;
            lastbid.LastMaxJobRunDT = newLot.CreatedOnDateTime;
            _lotlastbidchangeRepo.Insert(lastbid);

            //create junction record to track lot copies, splits,consolidates
            CreateLotLot(origProduct.Id, newProduct.Id, origLot.AULotID,  newLot.AULotID);

            var newConsignmentLot = new AUConsignmentLotRecord()
                {
                    AULotID = newLot.AULotID
                };

            foreach (var consignment in origLot.AUConsignmentRecords)
            {
                newConsignmentLot.AUConsignmentID = consignment.AUConsignmentID;
                _consignmentlotRepo.Insert(newConsignmentLot);
            }


            var newCountryLot = new AUCountryLotRecord()
            {
                AULotID = newLot.AULotID
            };

            foreach (var country in origLot.AUCountryRecords)
            {
                newCountryLot.AUCountryID = country.AUCountryID;
                _countrylotRepo.Insert(newCountryLot);
            }


            var newStateProvinceLot = new AUStateProvinceLotRecord()
            {
                AULotID = newLot.AULotID
            };

            foreach (var stateprovince in origLot.AUStateProvinceRecords)
            {
                newStateProvinceLot.AUStateProvinceID = stateprovince.AUStateProvinceID;
                _stateprovincelotRepo.Insert(newStateProvinceLot);
            }

            return newLot;
        }
        /// <summary>
        /// Inserts an AUCountryLot
        /// </summary>
        /// <param name="consignmentlot">AUCountryLotRecord</param>
        public virtual void InsertCountryLot(AUCountryLotRecord countrylot)
        {
            if (countrylot == null)
                throw new ArgumentNullException("countrylot");

            //insert
            _countrylotRepo.Insert(countrylot);

            //TODO: ADD LOT CACHE CONTROL AND PUBLISHING
            ////clear cache
            //_cacheManager.RemoveByPattern(PRODUCTS_PATTERN_KEY);

            ////event notification
            //_eventPublisher.EntityInserted(product);
        }