public static IList <ExcelProductUKViewModel> GenerateToExcelUK(IUnitOfWork db, IBarcodeService barcodeService, IMarketCategoryService categoryService, StyleViewModel model, DateTime when, out string filename) { var gbpExchangeRate = PriceHelper.GBPtoUSD; var models = new List <ExcelProductUKViewModel>(); var parent = new ExcelProductUKViewModel(); var childs = new List <ExcelProductUKViewModel>(); var sizes = model.StyleItems.Items; //Size Group, Size Range var hasKids2 = sizes.Any(s => ItemExportHelper.GetSizeGroupByName(s.SizeGroupName, true) == ExportSizeGroup.Kids2); ExportSizeGroup?sizeGroup = sizes.Any() ? (ExportSizeGroup?)ItemExportHelper.GetSizeGroupByName(sizes.Last().SizeGroupName, false) : null; var sizeGroupName = ItemExportHelper.GetSizeGroupName(sizeGroup); var sizeRange = ItemExportHelper.GetSizeRangeName(sizes.Select(s => s.Size).ToList(), hasKids2); var firstSize = sizes.Any() ? sizes[0].Size : String.Empty; //Features Values var features = model.Features.Select(f => new FeatureValueDTO() { FeatureId = f.FeatureId, Value = f.Value, }).ToList(); var allFeatureValues = db.FeatureValues.GetAllFeatureValueByItemType(1); var gender = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.GENDER); var itemStyle = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.ITEMSTYLE); var sleeve = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.SLEEVE); var material = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.MATERIAL); var color1 = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.COLOR1); var mainLicense = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.MAIN_LICENSE); var subLicense = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.SUB_LICENSE1); //var shippingSize = ItemExportHelper.GetFeatureValue(features, allFeatureValues, StyleFeatureHelper.SHIPPING_SIZE); var materialComposition = ItemExportHelper.GetFeatureTextValue(features, StyleFeatureHelper.MATERIAL_COMPOSITION); var sizeType = ItemExportHelper.GetSizeType(firstSize); var categoryInfo = categoryService.GetCategory(MarketType.AmazonEU, MarketplaceKeeper.AmazonUkMarketplaceId, itemStyle, gender, sizeType); //var itemType = ItemExportHelper.GetItemType(itemStyle); var brandName = ItemExportHelper.GetBrandName(mainLicense, subLicense); var newItemType = categoryInfo.Key1;// ItemExportHelper.ItemTypeConverter(firstSize ?? "", itemType, itemStyle, gender); var newDepartment = StringHelper.GetFirstNotEmpty(categoryInfo.Key2, ItemExportHelper.DepartmentConverter(gender, newItemType, sizeType)); var searchTerms = model.SearchTerms; // ItemExportHelper.BuildSearchTerms(itemStyle, material, sleeve); var keyFeatures = model.GetBulletPoints(); // ItemExportHelper.BuildKeyFeatures(mainLicense, subLicense, material); var hasCotton = StringHelper.GetInOneOfStrings("Cotton", new List <string>() { searchTerms, model.Name, model.Description, material }); if (String.IsNullOrEmpty(materialComposition)) { if (hasCotton) { materialComposition = "Cotton"; } else { materialComposition = "Polyester"; } } //-------------------------- //Parent item //-------------------------- parent.SKU = model.StyleId; parent.ASIN = ""; parent.Title = model.Name + "," + (!String.IsNullOrEmpty(sizeGroupName) ? " " + ItemExportHelper.FormatSizeGroupName(sizeGroupName) : String.Empty) + " " + sizeRange; //If it causes name length to exceed maximum allowed, please drop words “Kids” or Toddler” var inlcudeSizeGroup = true; if (parent.Title.Length > ItemExportHelper.MaxItemNameLength) { parent.Title = model.Name + ", " + sizeRange; inlcudeSizeGroup = false; } parent.ProductId = ""; parent.BrandName = brandName; parent.ClothingType = "sleepwear"; // = newItemType; var images = new List <string>(); var swatchImage = ""; if (model.ImageSet != null) { images = model.ImageSet.Images .Where(im => im.Category != (int)StyleImageCategories.Swatch) .OrderByDescending(im => ImageHelper.GetSortIndex(im.Category)) .ThenByDescending(im => im.IsDefault) .ThenBy(im => im.Id) .Select(im => im.ImageUrl) .ToList(); swatchImage = model.ImageSet.Images .FirstOrDefault(im => im.Category == (int)StyleImageCategories.Swatch)?.ImageUrl; parent.MainImageURL = images.Count > 0 ? images[0] : ""; parent.OtherImageUrl1 = images.Count > 1 ? images[1] : ""; parent.OtherImageUrl2 = images.Count > 2 ? images[2] : ""; parent.OtherImageUrl3 = images.Count > 3 ? images[3] : ""; //parent.SwatchImageUrl = swatchImage; } parent.Color = ""; parent.Department = newDepartment; parent.Size = ""; parent.MaterialComposition = materialComposition; parent.Description = model.Description; parent.Update = "Update"; parent.StandardPrice = ""; //parent.SuggestedPrice = model.MSRP.ToString("G"); parent.Currency = "GBP"; parent.Quantity = ""; parent.RecommendedBrowseNodes1 = ExcelProductUKViewModel.GetRecommendedBrowseNodes1(newDepartment, firstSize, newItemType); parent.SearchTerms1 = searchTerms; parent.KeyProductFeatures1 = keyFeatures.Count > 0 ? keyFeatures[0] : ""; parent.KeyProductFeatures2 = keyFeatures.Count > 1 ? keyFeatures[1] : ""; parent.KeyProductFeatures3 = keyFeatures.Count > 2 ? keyFeatures[2] : ""; parent.KeyProductFeatures4 = keyFeatures.Count > 3 ? keyFeatures[3] : ""; parent.KeyProductFeatures5 = keyFeatures.Count > 4 ? keyFeatures[4] : ""; parent.Parentage = "Parent"; parent.ParentSKU = ""; parent.RelationshipType = ""; parent.VariationTheme = "Size"; //-------------------------- //Child items //-------------------------- foreach (var size in sizes) { var child = new ExcelProductUKViewModel(); child.SKU = model.StyleId + "-" + ItemExportHelper.ConvertSizeForStyleId(size.Size, hasKids2); if (size.AutoGeneratedBarcode) { var newBarcode = BarcodeHelper.GenerateBarcode(barcodeService, child.SKU, when); if (!String.IsNullOrEmpty(newBarcode)) { if (size.Barcodes == null) { size.Barcodes = new List <BarcodeDTO>(); } size.Barcodes.Insert(0, new BarcodeDTO() { Barcode = newBarcode }); } } child.UPC = (size.Barcodes != null && size.Barcodes.Any()) ? size.Barcodes.FirstOrDefault().Barcode : String.Empty; child.Title = model.Name + "," + (inlcudeSizeGroup ? " " + ItemExportHelper.FormatSizeGroupName(size.SizeGroupName) : "") + " Size " + ItemExportHelper.ConvertSizeForItemName(size.Size, hasKids2); child.ProductId = "UPC"; child.BrandName = brandName; child.ClothingType = "sleepwear";// newItemType; child.MainImageURL = images.Count > 0 ? images[0] : ""; child.OtherImageUrl1 = images.Count > 1 ? images[1] : ""; child.OtherImageUrl2 = images.Count > 2 ? images[2] : ""; child.OtherImageUrl3 = images.Count > 3 ? images[3] : ""; //child.SwatchImageUrl = swatchImage; child.Color = ItemExportHelper.PrepareColor(String.IsNullOrEmpty(size.Color) ? color1 : size.Color); child.Department = newDepartment; child.Size = ExcelProductUKViewModel.SizeConverter(size.Size, material); child.MaterialComposition = materialComposition; child.Description = model.Description; child.Update = "Update"; child.StandardPrice = PriceHelper.Convert(model.Price, gbpExchangeRate, true).ToString("G"); //child.SuggestedPrice = model.MSRP.ToString("G"); child.Currency = "GBP"; child.Quantity = size.Quantity.ToString(); child.RecommendedBrowseNodes1 = ExcelProductUKViewModel.GetRecommendedBrowseNodes1(newDepartment, child.Size, newItemType); child.KeyProductFeatures1 = parent.KeyProductFeatures1; child.KeyProductFeatures2 = parent.KeyProductFeatures2; child.KeyProductFeatures3 = parent.KeyProductFeatures3; child.KeyProductFeatures4 = parent.KeyProductFeatures4; child.KeyProductFeatures5 = parent.KeyProductFeatures5; child.SearchTerms1 = parent.SearchTerms1; //child.SearchTerms2 = parent.SearchTerms2; //child.SearchTerms3 = parent.SearchTerms3; //child.SearchTerms4 = parent.SearchTerms4; //child.SearchTerms5 = parent.SearchTerms5; child.Parentage = "Child"; child.ParentSKU = parent.SKU; child.RelationshipType = "Variation"; child.VariationTheme = "Size"; childs.Add(child); } models.Add(parent); models.AddRange(childs); filename = model.StyleId + "_" + subLicense; filename = filename.Replace(" ", "") + "_UK.xls"; return(models); //ExcelHelper.ExportIntoFile(HttpContext.Current.Server.MapPath(UKTemplatePath), models); }