ProductImportEventSubscriber

ProductImportEventSubscriber.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Newtonsoft.Json;
using Polly;
using SmartStore.Core.Data;
using SmartStore.Core.Domain.Catalog;
using SmartStore.Core.Events;
using SmartStore.Core.Logging;
using SmartStore.Services.Catalog;
using SmartStore.Services.DataExchange.Import;
using SmartStore.Services.DataExchange.Import.Events;
using SmartStore.Services.Localization;
using SmartStore.Services.Seo;
using Sunify.WebApi.Etim.DataExchange;
using Sunify.WebApi.Etim.Models;
using Sunify.WebApi.Etim.Models.Classes;
using Sunify.WebApi.Etim.Models.DataExchange;

namespace Levasoft.ProductEtimHandling.Events
{
    public class ProductImportEventSubscriber : IConsumer
    {
        private readonly ILogger _logger;
        private bool _isEtimLoaded;
        private EtimClassSearchResponse? _etimApiInstance;
        private readonly IEtimProductMapper _etimProductMapper;
        private readonly ICategoryService _categoryService;
        private readonly ILocalizedEntityService _localizedEntityService;
        private readonly ILanguageService _languageService;
        private readonly IUrlRecordService _urlRecordService;
        private readonly ISpecificationAttributeService _specificationAttributeService;
        private readonly IRepository<ProductCategory> _productCategoryRepository;
        private List<ProductEtimMapping> _allEtimMappings;
        private int _processedRows;
        private string _subStoreEtimFolder;
        private int _totalRows;

        private const string EtimApiSchemaFile = "ETIM-Api-Metadata.json";
        private const string ArchiveFolder = "_archived";
        private const string ContentFolder = "Content";

        public ProductImportEventSubscriber(ILoggerFactory loggerFactory,
            IEtimProductMapper etimProductMapper,
            ICategoryService categoryService,
            ILocalizedEntityService localizedEntityService,
            ILanguageService languageService,
            IUrlRecordService urlRecordService,
            ISpecificationAttributeService specificationAttributeService,
            IRepository<ProductCategory> productCategoryRepository)
        {
            _isEtimLoaded = false;
            _logger = loggerFactory.GetLogger(nameof(ProductImportEventSubscriber));

            _etimProductMapper = etimProductMapper;
            _categoryService = categoryService;
            _localizedEntityService = localizedEntityService;
            _languageService = languageService;
            _urlRecordService = urlRecordService;
            _specificationAttributeService = specificationAttributeService;
            _productCategoryRepository = productCategoryRepository;
            _etimApiInstance = new EtimClassSearchResponse();
            _allEtimMappings = new List<ProductEtimMapping>();
            _processedRows = 0;
            _subStoreEtimFolder = string.Empty;
        }

        private static bool IsLastDataSegment(ImportDataSegmenter importDataSegmenter)
        {
            return importDataSegmenter.CurrentSegment ==
                   importDataSegmenter.TotalSegments;
        }

        public async Task HandleAsync(ImportBatchExecutedEvent<Product> msg)
        {
            #region INIT - Read ETIM Product Spezification Attributes (JSON File) into memory

            if (!await InitEtimFiles(msg))
            {
                return;
            }

            #endregion

            #region Persist Product - ETIM Mappings

            ImportRow<Product>[] importedProducts = msg.Batch as ImportRow<Product>[] ?? msg.Batch.ToArray();
            if (!importedProducts.Any()) return;

            var categoryQuery = _categoryService.BuildCategoriesQuery();

            if (_etimApiInstance == null)
            {
                _logger.Error($"{nameof(ProductImportEventSubscriber)} - ETIM API {nameof(_etimApiInstance)} is null.");
                return;
            }

            if (_etimApiInstance.Classes == null)
            {
                _logger.Error(
                    $"{nameof(ProductImportEventSubscriber)} - ETIM API {nameof(_etimApiInstance.Classes)} is null.");
                return;
            }

            IEnumerable<string?> etimLanguagecodes = _etimApiInstance.Classes
                .SelectMany(c => c.Translations)
                .Select(t => t.Languagecode)
                .Distinct()
                .ToArray();

            static bool IsEnglishLangCode(string langCode, IEnumerable<string?> etimLangCodes)
            {
                const string etimEnglishCode = "EN";
                string upperLangCode = langCode.ToUpperInvariant();

                return etimLangCodes.Contains(etimEnglishCode) &&
                       upperLangCode.StartsWith(etimEnglishCode);
            }

            var shopLanguages = _languageService
                .GetAllLanguages(showHidden: true, storeId: 0)
                .Where(l => etimLanguagecodes.Contains(l.LanguageCulture) ||
                            IsEnglishLangCode(l.LanguageCulture, etimLanguagecodes))
                .ToArray();

            IDbContext globalShopDbContext = msg.Context.Services.DbContext;

            bool prevAutoDetectChangesState = globalShopDbContext.AutoDetectChangesEnabled;
            bool prevAutoCommit = globalShopDbContext.AutoCommitEnabled;
            try
            {
                globalShopDbContext.AutoDetectChangesEnabled = true;
                globalShopDbContext.AutoCommitEnabled = true;

                #region Process each Product and apply ETIM Mappings

                foreach (ImportRow<Product> importedProduct in importedProducts)
                {
                    string mpn = string.Empty;
                    try
                    {
                        //Product? product = importedProduct.Entity;
                        mpn = importedProduct.GetDataValue<string>("ManufacturerPartNumber");

                        Product? product = importedProduct.Entity;

                        if (product == null)
                        {
                            _logger.Error($"{nameof(ProductImportEventSubscriber)} - No product in DB for MPN {mpn}");
                            continue;
                        }

                        ProductEtimMapping? etimItem =
                            _allEtimMappings.FirstOrDefault(pem => pem.ManufacturerPartNumber.Equals(mpn));

                        if (etimItem == null)
                        {
                            _logger.Error(
                                $"{nameof(ProductImportEventSubscriber)} - No ETIM Mapping found for Product with MPN {mpn}");
                            continue;
                        }

                        mpn = etimItem.ManufacturerPartNumber; // (XLSX Column A)
                        string etimCategory = etimItem.Category; // (XLSX Column B - ECxxxxxx)
                        var etimItemFeatureValuePairs =
                            etimItem.FeatureValuePairs; // (XLSX Key=Value Pairs beginning at Column C=D, E=F, etc.)

                        EtimClass? etimClass =
                            _etimApiInstance.Classes.FirstOrDefault(c => etimItem.Category.Equals(c.Code));

                        if (etimClass == null)
                        {
                            _logger.Error(
                                $"{nameof(ProductImportEventSubscriber)} - No ETIM Class Schema found for Product-ETIM Mapping Code {etimItem.Category} in {nameof(_etimApiInstance)}");
                            continue;
                        }

                        #region Clear all Product Category Mappings

                        IEnumerable<ProductCategory> prodCatMappings = _productCategoryRepository
                            .TableUntracked
                            .Where(x => x.ProductId == product.Id)
                            .ToList();

                        if (prodCatMappings.Any())
                        {
                            var removedCatIds = product.ProductCategories.Select(pcm => pcm.CategoryId);
                            string catIds = string.Join(", ", removedCatIds);
                            _logger.Info(
                                $"{nameof(ProductImportEventSubscriber)} - Remove that Category Ids {catIds} from product with Id {product.Id} / MPN {mpn} while ETIM processing.");

                            // clear current product-category mappings
                            await _productCategoryRepository.DeleteRangeAsync(prodCatMappings);
                        }

                        #endregion

                        #region Clear all SpecificaitonAttribute Mappings (They get reasigned again)

                        var currentProdSpecAttributes =
                            _specificationAttributeService.GetProductSpecificationAttributesByProductId(product.Id);

                        foreach (var productSpecificationAttribute in currentProdSpecAttributes)
                        {
                            _specificationAttributeService.DeleteProductSpecificationAttribute(
                                productSpecificationAttribute);
                        }

                        #endregion

                        #region Insert [Category] (EC) & UpSert [LocalizedProperty] / Language for [Category] (EC)

                        Category? category = categoryQuery.FirstOrDefault(c => etimCategory.Equals(c.Name.Trim()));
                        bool etimCategoryExists = category != null;

                        if (!etimCategoryExists)
                        {
                            string metaKeyWords = Stringified(etimClass.Synonyms);

                            category = new Category()
                            {
                                Name = etimCategory,
                                MetaTitle = etimClass.Description,
                                MetaKeywords = metaKeyWords,
                                Published = true,
                                Deleted = false,
                                DisplayOrder = 0
                            };

                            // insert ETIM ECxxxxxx -> Category
                            _categoryService.InsertCategory(category);
                        }

                        if (category == null)
                        {
                            _logger.Error(
                                $"{nameof(ProductImportEventSubscriber)} - Category is null for ETIM Code {etimCategory}");
                            continue;
                        }

                        #region Insert Product_Category_Mapping

                        var productCategory = new ProductCategory()
                        {
                            Category = category,
                            Product = product
                        };
                        product.ProductCategories.Add(productCategory);

                        #endregion

                        #region Upsert [LocalizedProperty] f. Category (EC) / Language

                        if (etimClass.Translations == null)
                        {
                            _logger.Error(
                                $"{nameof(ProductImportEventSubscriber)} - No ETIM Class.Translations found for Product-ETIM Mapping Code {etimItem.Category} in {nameof(_etimApiInstance)}");
                            continue;
                        }

                        // Insert Standard SLUG
                        string? standardseoName = category.ValidateSeName(etimClass.Description, etimClass.Description,
                            false, languageId: 0);
                        _urlRecordService.SaveSlug(category, standardseoName, languageId: 0);

                        foreach (var shopLanguage in shopLanguages)
                        {
                            EtimClassTranslation? etimClassTranslation = etimClass.Translations.FirstOrDefault(t =>
                                shopLanguage.LanguageCulture.Equals(t.Languagecode) ||
                                IsEtimEnglish(shopLanguage.LanguageCulture, t.Languagecode));


                            if (etimClassTranslation == null)
                            {
                                _logger.Warn(
                                    $"{nameof(ProductImportEventSubscriber)} - No ETIM Class (EC) Translation for language code {shopLanguage.LanguageCulture} found. ETIM Class {etimClass.Code}");
                                continue;
                            }

                            _localizedEntityService.SaveLocalizedValue(category, x => x.Name,
                                etimClassTranslation.Description, shopLanguage.Id);

                            #region Upsert LocalizedProperty f. Category SEO / Language and URL Slug

                            string localizedMetaKeyWords = Stringified(etimClassTranslation.Synonyms);

                            _localizedEntityService.SaveLocalizedValue(category, x => x.MetaTitle,
                                etimClassTranslation.Description, shopLanguage.Id);
                            _localizedEntityService.SaveLocalizedValue(category, x => x.MetaKeywords,
                                localizedMetaKeyWords,
                                shopLanguage.Id);

                            var seName = category.ValidateSeName(etimClassTranslation.Description,
                                etimClassTranslation.Description, false, shopLanguage.Id);
                            _urlRecordService.SaveSlug(category, seName, shopLanguage.Id);

                            #endregion
                        }

                        #endregion

                        #endregion

                        #region Upsert [SpecificationAttribute] (EF) & Upsert [LocalizedProperty] / [SpecificationAttribute] (EF) / Language

                        if (etimClass.Features == null)
                        {
                            _logger.Error(
                                $"{nameof(ProductImportEventSubscriber)} - No ETIM Class.Features found for Product-ETIM Mapping Code {etimItem.Category} in {nameof(_etimApiInstance)}");
                            continue;
                        }

                        static string Replace(string featureDescription) =>
                            featureDescription.ToLowerInvariant().Replace(' ', '-');

                        Dictionary<string, object>.KeyCollection usedEtimFeatures = etimItemFeatureValuePairs.Keys;

                        foreach (EtimClassFeature etimClassFeature in etimClass.Features)
                        {
                            if (string.IsNullOrWhiteSpace(etimClassFeature.Code))
                            {
                                _logger.Error(
                                    $"{nameof(ProductImportEventSubscriber)} - ETIM Feature (EF) Code is null or empty for Product-ETIM Mapping Code {etimItem.Category}");
                                continue;
                            }

                            if (!usedEtimFeatures.Contains(etimClassFeature.Code))
                            {
                                // only upsert used ETIM Features in Product (not all ETIM Features that are available)
                                continue;
                            }

                            var specificationAttribute = _specificationAttributeService
                                .GetSpecificationAttributes()
                                .FirstOrDefault(spec => etimClassFeature.Code!.Equals(spec.Name));

                            /*
                             *  •	[Name] <= ETIM Code
                                •	[Alias] <= ETIM Description.ToLowerCase().Replace(' ', '-')
                                •	[ShowOnProductPage] <= nur bei INSERT auf 1 (default)
                                •	[AllowFiltering] <= nur bei INSERT auf 1 (default)
                             */
                            if (specificationAttribute == null)
                            {
                                if (string.IsNullOrWhiteSpace(etimClassFeature.Description))
                                {
                                    _logger.Error(
                                        $"{nameof(ProductImportEventSubscriber)} - ETIM Feature (EF) Description is null or empty for Product-ETIM Mapping Code {etimItem.Category} / Feature {etimClassFeature.Code}");
                                    continue;
                                }

                                // insert
                                specificationAttribute = new SpecificationAttribute()
                                {
                                    Name = etimClassFeature.Code,
                                    Alias = Replace(etimClassFeature.Description!),
                                    ShowOnProductPage = true,
                                    AllowFiltering = true
                                };
                                _specificationAttributeService.InsertSpecificationAttribute(specificationAttribute);
                            }
                            else
                            {
                                // update
                                specificationAttribute.Name = etimClassFeature.Code;
                                specificationAttribute.Alias = Replace(etimClassFeature.Description!);

                                _specificationAttributeService.UpdateSpecificationAttribute(specificationAttribute);
                            }

                            if (etimClassFeature.Translations == null)
                            {
                                _logger.Error(
                                    $"{nameof(ProductImportEventSubscriber)} - No ETIM Feature.Translations found for Product-ETIM Mapping Code {etimItem.Category} / Feature {etimClassFeature.Code})");
                                continue;
                            }

                            /*
                             *  •	[EntityId] <= [SpecificationAttribute].[Id]
                                •	[LanguageId] <= ETIM Sprache gemappt aus [Language] tabelle
                                •	[LocaleKeyGroup] = 'SpecificationAttribute'
                                •	[LocaleKey] = 'Name'
                                •	[LocaleValue] <= ETIM sprachbezogene Übersetzung von dem Attribute
                             *
                             */
                            foreach (var shopLanguage in shopLanguages)
                            {
                                EtimTranslation? etimFeatureTranslation = etimClassFeature.Translations.FirstOrDefault(
                                    t =>
                                        shopLanguage.LanguageCulture.Equals(t.Languagecode) ||
                                        IsEtimEnglish(shopLanguage.LanguageCulture, t.Languagecode));

                                if (etimFeatureTranslation == null)
                                {
                                    _logger.Warn(
                                        $"{nameof(ProductImportEventSubscriber)} - No ETIM Feature (EF) Translation for language code {shopLanguage.LanguageCulture} found. ETIM Class {etimClass.Code} / Feature {etimClassFeature.Code}");
                                    continue;
                                }

                                _localizedEntityService.SaveLocalizedValue(specificationAttribute, spec => spec.Name,
                                    etimFeatureTranslation.Description, shopLanguage.Id);

                                _localizedEntityService.SaveLocalizedValue(specificationAttribute, spec => spec.Alias,
                                    Replace(etimFeatureTranslation.Description!), shopLanguage.Id);
                            }


                            #region Upsert [SpecificationAttributeOption] (EV) & Upsert [LocalizedProperty] / [SpecificationAttributeOption] (EV) / Language

                            /*
                             *  •	[SpecificationAttributeId] <= [SpecificationAttribute] .[Id]
                                •	[Name] <= ETIM Value Code Name *)
                                •	[Alias] <=  ETIM Value Code Description  **)
                            */
                            var etimFeatureValue = etimItem.FeatureValuePairs
                                .FirstOrDefault(fvp => etimClassFeature.Code!.Equals(fvp.Key));

                            if (etimFeatureValue.Value == null) continue;

                            string etimValue = etimFeatureValue.Value.ToString();

                            static string GetEtimValueDescription(EtimClassFeatureValue? etimClassFeatureValue) =>
                                etimClassFeatureValue?.Description ?? string.Empty;


                            bool hasEtimValueCode = etimValue.StartsWith("EV");

                            EtimClassFeatureValue? etimClassFeatureValue =
                                etimClassFeature.Values?.FirstOrDefault(v => etimValue.Equals(v.Code));

                            string etimValueDesciption = hasEtimValueCode
                                ? GetEtimValueDescription(etimClassFeatureValue)
                                : $"{specificationAttribute.Alias}-{etimValue.Replace(" ", string.Empty)}";

                            SpecificationAttributeOption? specificationAttributeOption =
                                _specificationAttributeService.GetSpecificationAttributeOptionById(
                                    specificationAttribute.Id);

                            if (specificationAttributeOption == null)
                            {
                                // Insert ETIM Value (EV) as SpecificationAttributeOptions
                                specificationAttributeOption = new SpecificationAttributeOption()
                                {
                                    SpecificationAttribute = specificationAttribute,
                                    Name = etimFeatureValue.Value.ToString(),
                                    Alias = etimValueDesciption
                                };

                                _specificationAttributeService.InsertSpecificationAttributeOption(
                                    specificationAttributeOption);
                            }
                            else
                            {
                                // Update SpecificationAttributeOption (ETIM Value (EV))
                                specificationAttributeOption.Name = etimFeatureValue.Value.ToString();
                                specificationAttributeOption.Alias = etimValueDesciption;

                                _specificationAttributeService.UpdateSpecificationAttributeOption(
                                    specificationAttributeOption);
                            }

                            var psa = new ProductSpecificationAttribute
                            {
                                SpecificationAttributeOption = specificationAttributeOption,
                                Product = product,
                                AllowFiltering = true,
                                ShowOnProductPage = true,
                                DisplayOrder = 0,
                            };

                            _specificationAttributeService.InsertProductSpecificationAttribute(psa);

                            // Upsert LocalizedProperty f. SpecificationAttributeOption (EV) / Language                        
                            if (!hasEtimValueCode) continue;

                            if (etimClassFeatureValue == null)
                            {
                                _logger.Error(
                                    $"{nameof(ProductImportEventSubscriber)} - No ETIM Value (EV) found for Product Feature Mapping {etimValue} on ETIM API Feature {etimClassFeature.Code} in ETIM (EC) Class {etimItem.Category}");
                                continue;
                            }

                            if (etimClassFeatureValue.Translations == null)
                            {
                                _logger.Error(
                                    $"{nameof(ProductImportEventSubscriber)} - No ETIM Value.Translation (EV) found for Product Feature Mapping {etimValue} on ETIM API Feature {etimClassFeature.Code} in ETIM (EC) Class {etimItem.Category}");
                                continue;
                            }

                            foreach (var shopLanguage in shopLanguages)
                            {
                                EtimTranslation? etimValueTranslation =
                                    etimClassFeatureValue.Translations.FirstOrDefault(
                                        t =>
                                            shopLanguage.LanguageCulture.Equals(t.Languagecode) ||
                                            IsEtimEnglish(shopLanguage.LanguageCulture, t.Languagecode));

                                if (etimValueTranslation == null)
                                {
                                    _logger.Warn(
                                        $"{nameof(ProductImportEventSubscriber)} - No ETIM Feature (EF) Translation for language code {shopLanguage.LanguageCulture} found. ETIM Class {etimClass.Code} / Feature {etimClassFeature.Code}");
                                    continue;
                                }

                                _localizedEntityService.SaveLocalizedValue(specificationAttributeOption,
                                    spec => spec.Name,
                                    etimValueTranslation.Description, shopLanguage.Id);

                                _localizedEntityService.SaveLocalizedValue(specificationAttributeOption,
                                    spec => spec.Alias,
                                    Replace(etimValueTranslation.Description!), shopLanguage.Id);
                            }

                            #endregion
                        }

                        #endregion

                        _processedRows++;
                        _logger.Info(
                            $"{nameof(ProductImportEventSubscriber)} - Product-Etim mapping row {_processedRows}/{_totalRows} processed for MPN: {mpn}");
                    }
                    catch (Exception e)
                    {
                        _logger.Error(e,
                            $"{nameof(ProductImportEventSubscriber)} - Product-Etim mapping couldn't be processed for MPN: {mpn}" +
                            Environment.NewLine +
                            e.Message);
                    }
                }

                #endregion
            }
            catch (Exception e)
            {
                _logger.Error(e, $"{nameof(ProductImportEventSubscriber)} - {e.Message}");
                return;
            }
            finally
            {
                globalShopDbContext.AutoDetectChangesEnabled = prevAutoDetectChangesState;
                globalShopDbContext.AutoCommitEnabled = prevAutoCommit;
            }

            #region finally - if last batch => move current product xlsx to archive

            if (IsLastDataSegment(msg.Context.DataSegmenter))
            {
                try
                {
                    var archiveFolder = @$"{msg.Context.ImportFolder}\{ArchiveFolder}";

                    if (!Directory.Exists(archiveFolder))
                    {
                        Directory.CreateDirectory(archiveFolder);
                    }

                    string archiveSubFolder = DateTimeOffset.Now.ToString("s")
                        .Replace("-", string.Empty)
                        .Replace(":", string.Empty);

                    string absArchiveFolder = Path.Combine(archiveFolder, archiveSubFolder);
                    if (!Directory.Exists(absArchiveFolder))
                    {
                        Directory.CreateDirectory(absArchiveFolder);
                    }

                    #region Try Move Product XLSX file

                    string absContentFolder = Path.Combine(msg.Context.ImportFolder, ContentFolder);
                    string sourceProductFile = Path.Combine(absContentFolder, msg.Context.File.Name);
                    string destProductFile = Path.Combine(absArchiveFolder, msg.Context.File.Name);

#pragma warning disable 4014 Needs to run un-awaited because we run in DataImporter.cs filestream using block that needs to end, after that we can move the file
                    Task.Run(() =>
                    {
                        // Will wait for
                        //  2 ^ 1 = 2 seconds then
                        //  2 ^ 2 = 4 seconds then
                        //  2 ^ 3 = 8 seconds then
                        //  2 ^ 4 = 16 seconds then
                        //  2 ^ 5 = 32 seconds
                        //  2 ^ 6 = 64 seconds 
                        // between each retry (exponential backoff)
                        const int maxRetry = 6;
                        Policy
                            .Handle<Exception>()
                            .WaitAndRetry(
                                maxRetry,
                                retryAttempt => TimeSpan.FromSeconds(Math.Pow(2, retryAttempt)),
                                (exception, timeSpan, retry, _) =>
                                {
                                    // Add logic to be executed before each retry, such as logging
                                    _logger.Error(exception,
                                        $"{nameof(ProductImportEventSubscriber)} Retry {retry}/{maxRetry} next move attempt in {timeSpan.TotalSeconds} sec. - ETIM file move error: {exception.Message}");
                                }
                            )
                            .Execute(() =>
                            {
                                File.Move(sourceProductFile, destProductFile);
                                _logger.Info($"Moved product file {msg.Context.File.Name} to {absArchiveFolder}");
                            });
                    }).ContinueWith(task =>
#pragma warning restore 4014
                        {
                            task.Exception?.Handle((ex) =>
                            {
                                try
                                {
                                    _logger.Error(task.Exception.Flatten().InnerException);
                                }
                                catch (Exception e)
                                {
                                    _logger.Error(e);
                                }

                                return true;
                            });
                        },
                        TaskContinuationOptions.OnlyOnFaulted);

                    #endregion
                }
                catch (Exception e)
                {
                    _logger.Error(e, $"{nameof(ProductImportEventSubscriber)} - ETIM file move error: {e.Message}");
                }
            }

            #endregion

            #endregion
        }

        private static string Stringified(IEnumerable<string>? synonyms)
        {
            return synonyms != null ? string.Join(", ", synonyms) : string.Empty;
        }

        private static bool IsEtimEnglish(string? shopLangCode, string? etimLangCode)
        {
            if (string.IsNullOrWhiteSpace(shopLangCode) ||
                string.IsNullOrWhiteSpace(etimLangCode)) return false;

            const string etimEnglishCode = "EN";
            string upperLangCode = shopLangCode!.ToUpperInvariant();

            return etimLangCode!.Equals(etimEnglishCode) &&
                   upperLangCode.StartsWith(etimEnglishCode);
        }

        private async Task<bool> InitEtimFiles(ImportBatchExecutedEvent<Product> msg)
        {
            try
            {
                if (_isEtimLoaded) return true;

                _totalRows = msg.Context.DataSegmenter.TotalRows;

                // Read all ETIM Files in {ImportProfileFolder}/Etim into memory (etimItems)

                #region RegEx Product-ETIM FileFormat Parser

                string inputFileName = msg.Context.File.Name;

                var regex = new Regex(@"(?<Filename>.{1,})-(?<SubStoreId>\d{1,})-sunified(?<Ext>.\w{0,})?$",
                    RegexOptions.IgnoreCase);
                Match match = regex.Match(inputFileName);

                if (!match.Success)
                {
                    _logger.Error(
                        $"{nameof(ProductImportEventSubscriber)} - No valid regex match for product excel file: {inputFileName}");
                    return false;
                }

                string fileName = match.Groups["Filename"].Value;
                string subStoreId = match.Groups["SubStoreId"].Value;
                string ext = match.Groups["Ext"].Value;
                _logger.Info(
                    $"{nameof(ProductImportEventSubscriber)} - ETIM processing of product excel file '{fileName}{ext}' in sub-store {subStoreId}");

                #endregion


                _subStoreEtimFolder = @$"{msg.Context.ImportFolder}\Etim\{subStoreId}";
                var etimApiFile = @$"{_subStoreEtimFolder}\{EtimApiSchemaFile}";


                #region Deserialize ETIM API File

                using StreamReader streamReader = File.OpenText(etimApiFile);
                string json = await streamReader.ReadToEndAsync().ConfigureAwait(false);
                _etimApiInstance = JsonConvert.DeserializeObject<EtimClassSearchResponse>(json);

                if (_etimApiInstance == null)
                {
                    _logger.Error(
                        $"{nameof(ProductImportEventSubscriber)} - ETIM API File is not deserializable. File: {etimApiFile}" +
                        Environment.NewLine +
                        json);
                    return false;
                }

                #endregion

                #region Deserialize Product-ETIM Mapping File(s)

                var etimFileInfos = new DirectoryInfo(_subStoreEtimFolder)
                    .GetFiles("*.json")
                    .Where(fi => !fi.Name.Equals(EtimApiSchemaFile));

                foreach (var etimFileInfo in etimFileInfos)
                {
                    var etimInstance = await _etimProductMapper.DeserializeAsync(etimFileInfo.FullName);
                    _allEtimMappings = _allEtimMappings.Union(etimInstance).ToList();
                }

                #endregion

                _isEtimLoaded = true;
                return true;
            }
            catch (Exception e)
            {
                _logger.Error(e, $"{nameof(ProductImportEventSubscriber)} - {e.Message}");
            }

            return false;
        }
    }
}