public static MemoryStream ExportPickList(long pickListId, IUnitOfWork db, ITime time, out string filename) { //var resultItems = new List<ExcelProductUSViewModel>(); var pickList = db.FBAPickLists.GetAllAsDto(ShipmentsTypeEnum.None).FirstOrDefault(p => p.Id == pickListId); var pickListEntries = (from e in db.FBAPickListEntries.GetAll() join l in db.Listings.GetAll() on e.ListingId equals l.Id join i in db.Items.GetAll() on l.ItemId equals i.Id join st in db.Styles.GetAll() on i.StyleId equals st.Id where e.FBAPickListId == pickListId select new { Id = e.Id, StyleId = i.StyleId, SKU = l.SKU, Quantity = e.Quantity, Barcode = i.Barcode, Title = st.Name, }).ToList(); var styleIds = pickListEntries.Select(e => e.StyleId).ToList(); var locations = db.StyleLocations.GetAll().Where(l => styleIds.Contains(l.StyleId)) .OrderByDescending(l => l.IsDefault) .ThenBy(l => l.Id) .ToList(); IList <ExcelWFSLocationViewModel> locationItems = new List <ExcelWFSLocationViewModel>(); foreach (var i in pickListEntries) { var location = locations.FirstOrDefault(l => l.StyleId == i.StyleId); locationItems.Add(new ExcelWFSLocationViewModel() { PickListEntryId = i.Id, SKU = ItemExportHelper.PrepareSKU(i.SKU, ExportToExcelMode.FBA), Quantity = i.Quantity, Barcode = i.Barcode, Title = i.Title, Location = location != null ? location.Isle + "/" + location.Section + "/" + location.Shelf : "", LocationIndex = location != null ? location.SortIsle * 100000 + location.SortSection * 1000 + location.SortShelf : Int32.MaxValue, }); } locationItems = locationItems .OrderBy(l => l.LocationIndex) .ThenBy(l => l.PickListEntryId) .ToList(); var output = ExcelHelper.Export(locationItems, null, isXlsx: true); filename = String.Format(pickList.FBAPickListType + "_PickList_{0}.xlsx", pickList.CreateDate.ToString("MM-dd-yyyy")); return(output); }
public static MemoryStream ExportToWFSPlanExcel(long pickListId, IUnitOfWork db, IDbFactory dbFactory, CompanyDTO company, ILogService log, ITime time, out string filename) { //var resultItems = new List<ExcelProductUSViewModel>(); var pickList = db.FBAPickLists.GetAllAsDto(ShipmentsTypeEnum.WFS).FirstOrDefault(p => p.Id == pickListId); var pickListEntries = db.FBAPickListEntries.GetAllAsDto() .Where(p => p.FBAPickListId == pickListId) .ToList(); var fbaPlanItems = pickListEntries.Select(e => new ExcelWFSPlanViewModel() { SKU = ItemExportHelper.PrepareSKU(e.ListingSKU, ExportToExcelMode.FBA), Quantity = e.Quantity, UnitsPerCase = e.Quantity, NumberOfCases = 1 }).ToList(); var output = ExcelHelper.ExportIntoFile(HttpContext.Current.Server.MapPath(ExcelWFSPlanViewModel.TemplatePath), "sampleInboundShipmentTemplate 1", fbaPlanItems, customData: new List <ExcelHelper.CustomField>() { new ExcelHelper.CustomField() { Row = 0, Cell = 1, Value = "WFS-" + pickList.CreateDate.ToString("MM-dd-yyyy") } }, headerRowOffset: 12); filename = String.Format("WFSPlan_{0}.xls", time.GetAppNowTime().ToString("MM_dd_yyyy_hh_mm_ss")); return(output); }
public static IList <ExcelWFSProductUSViewModel> GetItemsFor(ILogService log, ITime time, IMarketCategoryService categoryService, IHtmlScraperService htmlScraper, IMarketApi marketApi, IUnitOfWork db, CompanyDTO company, string asin, ExportToExcelMode exportMode, IList <FBAItemInfo> fbaItems, MarketType market, string marketplaceId, UseStyleImageModes useStyleImageMode, out string filename) { filename = null; var items = ItemExportHelper.GetParentItemAndChilds(log, time, htmlScraper, marketApi, db, company, asin, fbaItems, exportMode, market, marketplaceId); var parent = items.Item1; // db.ParentItems.GetAsDTO(asin); var children = items.Item2; // db.Items.GetAllActualExAsDto().Where(i => i.ParentASIN == asin).ToList(); var models = GetItemsFor(db, categoryService, exportMode, fbaItems, parent, children, useStyleImageMode, out filename); return(models); }
public static IList <ExcelWFSProductUSViewModel> GenerateToExcelUS(IUnitOfWork db, IBarcodeService barcodeService, IMarketCategoryService categoryService, StyleViewModel model, DateTime when, out string filename) { /* * 1. Item_sku: * a. First row = Style * b. Other rows – Style-%size%, i.e. K123123-2T, K123123-3T.. * 2. Item name: * a. First Raw - %Name% + “,”+%Size Group% (Infant, Toddler, Kids)+”Sizes”+%Size_Range%” (example: Planes Boys 'Turn Up the Heat' Coat Style Pajama Set, Toddler Sizes 2T-4T) * b. Other rows: %Name% + “,”+%Size Group% (Infant, Toddler, Kids)+%Size% (i.e. Planes Boys 'Turn Up the Heat' Coat Style Pajama Set, Toddler Size 2T) * 3. external_product_id – barcode (empty for first raw) * 4. external_product_id_type – harcoded “UPC” (empty for first raw) * 16. brand_name - Main License * 17. item_type – use same logic as you use when you generate xls from ASIN * 18. main_image_url – upload image to server, and insert public URL to this image here * 19. Color - %Color% * 20. Department_name - use same logic as you use when you generate xls from ASIN * 21. Size - %size% * 22. update_delete – “Update” * 23. standard_price - %price% * 24. list_price -- %MSRP% * 25. currency – “USD” * 26. quantity – “%Quantity% * 27. bullet_point1 – “Authentic ”+ %Main License% +” product with reliable quality and durability * 28. bullet_point2 – “Featuring “+%sublicense% * 29. bullet_point3 – if(Material != Cotton) “Flame resistant” else “100% Cotton” * 30. bullet_point4 – “Machine Wash, Easy Care” * 31. generic_keywords1 = if(Item style = pajama) “ sleepwear, pj, jummie, new, %sleeve%” * if(nightgown) “night, gown, night-gown, sleepwear, pj, jummie, new, %sleeve%, dress up” * 32. “Gift, present, 2015, cozy ” If(Material =fleece) “Fleece, microfleece, warm, winter spring” * 33. parent_child, parent_sku, relationship_type, variation_theme – same logic as you use when you generate xls from ASIN * 34. if More then one picture provided insert their URL into other_image_url1, other_image_url2, other_image_url3 */ var models = new List <ExcelWFSProductUSViewModel>(); var parent = new ExcelWFSProductUSViewModel(); var childs = new List <ExcelWFSProductUSViewModel>(); 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, Feature.SHIPPING_SIZE); //var itemType = ItemExportHelper.GetItemType(itemStyle); var sizeType = ItemExportHelper.GetSizeType(firstSize); var categoryInfo = categoryService.GetCategory(MarketType.Amazon, MarketplaceKeeper.AmazonComMarketplaceId, itemStyle, gender, sizeType); 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)); string clothingType = categoryInfo.Key3; var searchTerms = model.SearchTerms; // ItemExportHelper.BuildSearchTerms(itemStyle, material, sleeve); var keyFeatures = model.GetBulletPoints(); // ItemExportHelper.BuildKeyFeatures(mainLicense, subLicense, material); //-------------------------- //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.Type = 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 = "";// color1; parent.Department = newDepartment; parent.Size = ""; parent.Description = model.Description; parent.Update = "Update"; parent.StandardPrice = ""; parent.SuggestedPrice = Math.Round(model.MSRP).ToString("G"); parent.Currency = "USD"; parent.Quantity = ""; 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.SearchTerms1 = searchTerms; //parent.SearchTerms3 = ""; //parent.SearchTerms4 = ""; //parent.SearchTerms5 = ""; parent.Parentage = "Parent"; parent.ParentSKU = ""; parent.RelationshipType = ""; parent.VariationTheme = "Size"; parent.FeedProductType = clothingType; //-------------------------- //Child items //-------------------------- foreach (var size in sizes) { var child = new ExcelWFSProductUSViewModel(); child.SKU = model.StyleId + "-" + ItemExportHelper.ConvertSizeForStyleId(size.Size, hasKids2); child.StyleItemId = size.Id; child.StyleId = model.Id; 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.ASIN = (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.Type = 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 = size.Size; child.Description = model.Description; child.Update = "Update"; child.StandardPrice = model.Price.ToString("G"); child.SuggestedPrice = Math.Round(model.MSRP).ToString("G"); child.Currency = "USD"; child.Quantity = size.Quantity.ToString(); 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.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"; child.FeedProductType = clothingType; childs.Add(child); } models.Add(parent); models.AddRange(childs); filename = model.StyleId + "_" + subLicense; filename = filename.Replace(" ", "") + "_US.xls"; return(models); }
public static IList <ExcelWFSProductUSViewModel> GetItemsFor(IUnitOfWork db, IMarketCategoryService categoryService, ExportToExcelMode exportMode, IList <FBAItemInfo> fbaItems, ParentItemDTO parent, IList <ItemExDTO> children, UseStyleImageModes useStyleImageMode, out string filename) { var models = new List <ExcelWFSProductUSViewModel>(); filename = null; var defaultChild = children.OrderByDescending(ch => ch.IsExistOnAmazon).ThenBy(ch => ch.Id).FirstOrDefault(); if (!children.Any() && exportMode != ExportToExcelMode.Normal) { return(models); } FeatureValueDTO defaultChildSubLicense = null; var defaultChildItemStyle = String.Empty; var defaultChildGender = String.Empty; var defaultChildBrand = String.Empty; StyleEntireDto firstChildStyle = null; string mainBrandName = null; decimal? childListPrice = null; string childDepartment = null; string childSize = null; string childStyleString = null; IList <string> defaultChildFeatures = new List <string>(); if (defaultChild != null && defaultChild.StyleId.HasValue) { defaultChildSubLicense = db.FeatureValues.GetValueByStyleAndFeatureId(defaultChild.StyleId.Value, StyleFeatureHelper.SUB_LICENSE1); defaultChildItemStyle = ItemExportHelper.GetFeatureValue(db.FeatureValues.GetValueByStyleAndFeatureId(defaultChild.StyleId.Value, StyleFeatureHelper.ITEMSTYLE)); defaultChildGender = ItemExportHelper.GetFeatureValue(db.FeatureValues.GetValueByStyleAndFeatureId(defaultChild.StyleId.Value, StyleFeatureHelper.GENDER)); defaultChildBrand = ItemExportHelper.GetFeatureValue(db.FeatureValues.GetValueByStyleAndFeatureId(defaultChild.StyleId.Value, StyleFeatureHelper.BRAND)); mainBrandName = StringHelper.GetFirstNotEmpty(parent.BrandName, defaultChild.BrandName, defaultChildBrand); defaultChildFeatures = !String.IsNullOrEmpty(defaultChild.Features) ? defaultChild.Features.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList() : new List <string>(); childListPrice = defaultChild.ListPrice; childDepartment = defaultChild.Department; childSize = defaultChild.Size; childStyleString = defaultChild.StyleString; if (defaultChild.StyleId.HasValue) { firstChildStyle = db.Styles.GetByStyleIdAsDto(defaultChild.StyleId.Value); } } if (parent != null) { var variationType = ItemExportHelper.GetVariationType(children != null ? children.Select(ch => ch.Color).ToList() : null); //For simplicity, w can just use for parent SKU (unless it exist) style-%parentASIN%. string parentSku = parent.SKU; if (String.IsNullOrEmpty(parentSku)) { //NOTE: removed ASIN part (I guess it could be used for preventing listings mix) parentSku = childStyleString;// + "-" + parent.ASIN; } var mainGender = StringHelper.GetFirstNotEmpty(childDepartment, parent.Department, defaultChildGender); var sizeType = ItemExportHelper.GetSizeType(childSize); var categoryInfo = categoryService.GetCategory((MarketType)parent.Market, parent.MarketplaceId, defaultChildItemStyle, mainGender, sizeType); //var type = ItemExportHelper.GetItemType(itemStyle); var mainItemType = categoryInfo.Key1;// ItemExportHelper.ItemTypeConverter(childSize ?? "", type, itemStyle, gender); var mainDepartment = StringHelper.GetFirstNotEmpty(categoryInfo.Key2, ItemExportHelper.DepartmentConverter(mainGender, mainItemType, sizeType)); var parentImage = ItemExportHelper.ImageConverter(StringHelper.GetFirstNotEmpty( parent.LargeImage != null ? parent.LargeImage.Image : null, parent.ImageSource)); string clothingType = categoryInfo.Key3; var searchTerms = (parent.SearchKeywords ?? "").Replace(";", ", "); searchTerms = ItemExportHelper.PrepareSearchTerms(searchTerms); var mainMsrp = firstChildStyle != null && firstChildStyle.MSRP.HasValue ? firstChildStyle.MSRP.Value.ToString("G") : (childListPrice.HasValue ? Math.Round(childListPrice.Value / 100).ToString("G") : ""); var mainDescription = firstChildStyle?.Description ?? parent.Description; models.Add(new ExcelWFSProductUSViewModel { SKU = parentSku, Title = parent.AmazonName, ASIN = parent.ASIN, ProductId = "ASIN", BrandName = mainBrandName, Description = mainDescription, Type = mainItemType, Update = "Update", StandardPrice = "", SuggestedPrice = mainMsrp, Currency = childListPrice.HasValue ? "USD" : "", Quantity = "", KeyProductFeatures1 = defaultChildFeatures.Count > 0 ? defaultChildFeatures[0] : String.Empty, KeyProductFeatures2 = defaultChildFeatures.Count > 1 ? defaultChildFeatures[1] : String.Empty, KeyProductFeatures3 = defaultChildFeatures.Count > 2 ? defaultChildFeatures[2] : String.Empty, KeyProductFeatures4 = defaultChildFeatures.Count > 3 ? defaultChildFeatures[3] : String.Empty, KeyProductFeatures5 = defaultChildFeatures.Count > 4 ? defaultChildFeatures[4] : String.Empty, SearchTerms1 = searchTerms, //SearchTerms1 = searchTermList.Count > 0 ? searchTermList[0] : String.Empty, //SearchTerms2 = searchTermList.Count > 1 ? searchTermList[1] : String.Empty, //SearchTerms3 = searchTermList.Count > 2 ? searchTermList[2] : String.Empty, //SearchTerms4 = searchTermList.Count > 3 ? searchTermList[3] : String.Empty, //SearchTerms5 = searchTermList.Count > 4 ? searchTermList[4] : String.Empty, MainImageURL = parentImage, Parentage = ExcelHelper.ParentageParent, VariationTheme = variationType, Department = mainDepartment, Color = "", FeedProductType = clothingType, }); foreach (var child in children) { var childStyleImages = child.StyleId.HasValue ? db.StyleImages .GetAllAsDto() .Where(im => im.StyleId == child.StyleId.Value && im.Type != (int)StyleImageType.Swatch) .ToList() .OrderByDescending(im => ImageHelper.GetSortIndex(im.Category)) .ThenByDescending(im => im.IsDefault) .ThenBy(im => im.Id) .ToList() : null; var childStyleGenderValue = child.StyleId.HasValue ? db.StyleFeatureValues.GetAllWithFeature() .FirstOrDefault(st => st.StyleId == child.StyleId.Value && st.FeatureId == StyleFeatureHelper.GENDER)?.Value : null; var childStyle = child.StyleId.HasValue ? db.Styles.GetByStyleIdAsDto(child.StyleId.Value) : null; var msrp = childStyle != null && childStyle.MSRP.HasValue ? childStyle.MSRP.Value.ToString("G") : (child.ListPrice.HasValue ? Math.Round(child.ListPrice.Value / 100).ToString("G") : ""); var description = childStyle?.Description ?? parent.Description; var fbaInfo = fbaItems != null?fbaItems.FirstOrDefault(f => f.SKU == child.SKU) : null; if (exportMode == ExportToExcelMode.FBA) { if (fbaInfo == null) { throw new ArgumentNullException("fbaInfo", "for SKU=" + child.SKU); } } IList <string> features = !String.IsNullOrEmpty(child.Features) ? child.Features.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList() : new List <string>(); if (!String.IsNullOrEmpty(childStyle.BulletPoint1)) { features = StringHelper.ToArray(childStyle.BulletPoint1, childStyle.BulletPoint2, childStyle.BulletPoint3, childStyle.BulletPoint4, childStyle.BulletPoint5); } var childImage = ItemExportHelper.ImageConverter(StringHelper.GetFirstNotEmpty( child.LargeImageUrl, child.ImageUrl)); var childImage1 = ""; var childImage2 = ""; var childImage3 = ""; if (useStyleImageMode == UseStyleImageModes.Auto) { if (String.IsNullOrEmpty(childImage)) { if (childStyleImages != null && childStyleImages.Any()) { childImage = childStyleImages.First().Image; } } else { if (childStyleImages != null && childStyleImages.Any(im => im.Type == (int)StyleImageType.HiRes)) { childImage = childStyleImages.First(im => im.Type == (int)StyleImageType.HiRes).Image; } } } if (useStyleImageMode == UseStyleImageModes.StyleImage || (useStyleImageMode == UseStyleImageModes.ListingImage && String.IsNullOrEmpty(childImage))) { if (childStyleImages != null && childStyleImages.Any()) { childImage = childStyleImages.First().Image; childImage1 = childStyleImages.Count > 1 ? childStyleImages[1].Image : ""; childImage2 = childStyleImages.Count > 2 ? childStyleImages[2].Image : ""; childImage3 = childStyleImages.Count > 3 ? childStyleImages[3].Image : ""; } } if (useStyleImageMode == UseStyleImageModes.ListingImage) { //Nothing } var hasAmazonUpdates = child.IsExistOnAmazon == true; var displaySize = String.IsNullOrEmpty(child.Size) ? "" : ("Size " + child.Size); models.Add(new ExcelWFSProductUSViewModel { Id = child.Id, StyleId = child.StyleId, StyleItemId = child.StyleItemId, IsExistOnAmazon = hasAmazonUpdates, SKU = ItemExportHelper.PrepareSKU(child.SKU, exportMode), Title = hasAmazonUpdates ? child.Name : StringHelper.Join(", ", child.Name, child.Color, displaySize), ASIN = hasAmazonUpdates ? child.ASIN : child.Barcode, ProductId = hasAmazonUpdates ? "ASIN" : "UPC", BrandName = mainBrandName, Description = description, Type = mainItemType, Update = hasAmazonUpdates ? "PartialUpdate" : "Update", StandardPrice = (child.CurrentPrice + (exportMode == ExportToExcelMode.FBA || exportMode == ExportToExcelMode.FBP ? 5 : 0)).ToString("G"), SuggestedPrice = msrp, Currency = child.ListPrice.HasValue ? "USD" : "", Quantity = exportMode == ExportToExcelMode.FBA ? fbaInfo.Quantity.ToString("G") : child.RealQuantity.ToString("G"), KeyProductFeatures1 = features.Count > 0 ? features[0] : String.Empty, KeyProductFeatures2 = features.Count > 1 ? features[1] : String.Empty, KeyProductFeatures3 = features.Count > 2 ? features[2] : String.Empty, KeyProductFeatures4 = features.Count > 3 ? features[3] : String.Empty, KeyProductFeatures5 = features.Count > 4 ? features[4] : String.Empty, SearchTerms1 = searchTerms, //SearchTerms1 = searchTermList.Count > 0 ? searchTermList[0] : String.Empty, //SearchTerms2 = searchTermList.Count > 1 ? searchTermList[1] : String.Empty, //SearchTerms3 = searchTermList.Count > 2 ? searchTermList[2] : String.Empty, //SearchTerms4 = searchTermList.Count > 3 ? searchTermList[3] : String.Empty, //SearchTerms5 = searchTermList.Count > 4 ? searchTermList[4] : String.Empty, MainImageURL = childImage, OtherImageUrl1 = childImage1, OtherImageUrl2 = childImage2, OtherImageUrl3 = childImage3, Parentage = ExcelHelper.ParentageChild, ParentSKU = parentSku, RelationshipType = "Variation", VariationTheme = variationType, Department = mainDepartment, Color = child.Color, Size = child.Size, //SpecialSize = child.SpecialSize, FulfillmentCenterID = exportMode == ExportToExcelMode.FBA ? "AMAZON_NA" : null, PackageHeight = exportMode == ExportToExcelMode.FBA && fbaInfo.PackageHeight.HasValue ? fbaInfo.PackageHeight.ToString() : null, PackageWidth = exportMode == ExportToExcelMode.FBA && fbaInfo.PackageWidth.HasValue ? fbaInfo.PackageWidth.ToString() : null, PackageLength = exportMode == ExportToExcelMode.FBA && fbaInfo.PackageLength.HasValue ? fbaInfo.PackageLength.ToString() : null, PackageLengthUnitOfMeasure = exportMode == ExportToExcelMode.FBA ? "IN" : null, PackageWeight = exportMode == ExportToExcelMode.FBA ? child.Weight.ToString() : null, PackageWeightUnitOfMeasure = exportMode == ExportToExcelMode.FBA ? "OZ" : null, SupplierDeclaredDgHzRegulation1 = "Not Applicable", BatteriesRequired = "FALSE", FeedProductType = clothingType, MerchantShippingGroupName = child.IsPrime ? AmazonTemplateHelper.PrimeTemplate : (child.Weight > 16 ? AmazonTemplateHelper.OversizeTemplate : null), }); } //Copy to parent child Type if (children.Count > 0) { models[0].Type = models[1].Type; } if (children.Count > 0 && useStyleImageMode == UseStyleImageModes.StyleImage) { models[0].MainImageURL = models[1].MainImageURL; } } filename = models[0].SKU + "_" + (defaultChildSubLicense != null ? defaultChildSubLicense.Value : "none") + "_US" + Path.GetExtension(USTemplatePath); return(models); }
public static IList <ExcelProductCAViewModel> GetItemsFor(ILogService log, ITime time, IMarketCategoryService categoryService, IHtmlScraperService htmlScraper, IMarketApi marketApi, IUnitOfWork db, CompanyDTO company, string parentAsin, MarketType market, string marketplaceId, UseStyleImageModes useStyleImageMode, out string filename) { var caExchangeRate = PriceHelper.CADtoUSD; var today = time.GetAppNowTime(); var models = new List <ExcelProductCAViewModel>(); var items = ItemExportHelper.GetParentItemAndChilds(log, time, htmlScraper, marketApi, db, company, parentAsin, null, ExportToExcelMode.Normal, market, marketplaceId); var parent = items.Item1; // db.ParentItems.GetAsDTO(asin); var children = items.Item2; // db.Items.GetAllActualExAsDto().Where(i => i.ParentASIN == asin).ToList(); var firstChild = children.FirstOrDefault(); FeatureValueDTO subLicense = null; string itemStyle = null; if (firstChild != null && firstChild.StyleId.HasValue) { //style = db.Styles.Get(parent.StyleId.Value); subLicense = db.FeatureValues.GetValueByStyleAndFeatureId(firstChild.StyleId.Value, StyleFeatureHelper.SUB_LICENSE1); itemStyle = ItemExportHelper.GetFeatureValue(db.FeatureValues.GetValueByStyleAndFeatureId(firstChild.StyleId.Value, StyleFeatureHelper.ITEMSTYLE)); } if (parent != null) { var variationType = ItemExportHelper.GetVariationType(children != null ? children.Select(ch => ch.Color).ToList() : null); string childBrandName = null; decimal? childListPrice = null; string childDepartment = null; string childSize = null; string childStyleString = null; IList <string> childFeatures = new List <string>(); if (firstChild != null) { childBrandName = children.Select(c => c.BrandName).FirstOrDefault(); childFeatures = !String.IsNullOrEmpty(firstChild.Features) ? firstChild.Features.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList() : new List <string>(); childListPrice = firstChild.ListPrice; childDepartment = firstChild.Department; childSize = firstChild.Size; childStyleString = firstChild.StyleString; } //For simplicity, w can just use for parent SKU (unless it exist) style-%parentASIN%. string parentSku = parent.SKU; if (String.IsNullOrEmpty(parentSku)) { parentSku = childStyleString + "-" + parent.ASIN; } var gender = (childDepartment ?? parent.Department ?? ""); var sizeType = ItemExportHelper.GetSizeType(childSize); var categoryInfo = categoryService.GetCategory(market, marketplaceId, itemStyle, gender, sizeType); //var itemType = ItemExportHelper.GetItemType(itemStyle); var newItemType = categoryInfo.Key1;// ItemExportHelper.ItemTypeConverter(childSize ?? "", itemType, itemStyle, gender); var newDepartment = StringHelper.GetFirstNotEmpty(categoryInfo.Key2, ItemExportHelper.DepartmentConverter(gender, newItemType, sizeType)); var parentImage = ItemExportHelper.ImageConverter(StringHelper.GetFirstNotEmpty( parent.LargeImage != null ? parent.LargeImage.Image : null, parent.ImageSource)); models.Add(new ExcelProductCAViewModel { SKU = parentSku,// parent.StyleString, Title = parent.AmazonName, ASIN = parent.ASIN, ProductId = "ASIN", ClothingType = "sleepwear", Manufacturer = parent.BrandName ?? childBrandName, BrandName = parent.BrandName ?? childBrandName, Description = parent.Description, Type = newItemType, Update = "Update", StandardPrice = "", SuggestedPrice = childListPrice.HasValue ? Math.Round(PriceHelper.Convert(childListPrice.Value / 100, caExchangeRate, true)).ToString("G") : "", Currency = childListPrice.HasValue ? "CAD" : "", Quantity = "", KeyProductFeatures1 = childFeatures.Count > 0 ? childFeatures[0] : String.Empty, KeyProductFeatures2 = childFeatures.Count > 1 ? childFeatures[1] : String.Empty, KeyProductFeatures3 = childFeatures.Count > 2 ? childFeatures[2] : String.Empty, KeyProductFeatures4 = childFeatures.Count > 3 ? childFeatures[3] : String.Empty, KeyProductFeatures5 = childFeatures.Count > 4 ? childFeatures[4] : String.Empty, RecommendedBrowseNodes1 = ExcelProductCAViewModel.GetRecommendedBrowseNodes1(newDepartment, childSize, newItemType), MainImageURL = parentImage, Parentage = "Parent", VariationTheme = variationType, Department = newDepartment, Color = "", }); foreach (var child in children) { var styleImages = child.StyleId.HasValue ? db.StyleImages .GetAllAsDto() .Where(im => im.StyleId == child.StyleId.Value && im.Type != (int)StyleImageType.Swatch) .ToList() .OrderByDescending(im => ImageHelper.GetSortIndex(im.Category)) .ThenByDescending(im => im.IsDefault) .ThenBy(im => im.Id) .ToList() : null; IList <string> features = !String.IsNullOrEmpty(child.Features) ? child.Features.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList() : new List <string>(); var childImage = ItemExportHelper.ImageConverter(StringHelper.GetFirstNotEmpty( child.LargeImageUrl, child.ImageUrl)); if (useStyleImageMode == UseStyleImageModes.StyleImage) { if (styleImages != null && styleImages.Any()) { childImage = styleImages.First().Image; } } var itemPrice = child.SalePrice.HasValue && child.SaleStartDate < today ? child.SalePrice.Value : child.CurrentPrice; models.Add(new ExcelProductCAViewModel { StyleId = child.StyleId, StyleItemId = child.StyleItemId, SKU = child.SKU, Title = child.Name, ASIN = child.ASIN, ProductId = "ASIN", ClothingType = "sleepwear", Manufacturer = child.BrandName, BrandName = child.BrandName, Description = parent.Description, Type = newItemType, Update = "Update", //StandardPrice = child.Price.ToString("G"), StandardPrice = CorrectCAPrice(PriceHelper.Convert(itemPrice, caExchangeRate, true)).ToString("G"), //SalePrice = child.SalePrice.HasValue && child.SaleStartDate < today ? CorrectCAPrice(PriceHelper.Convert(child.SalePrice.Value, caExchangeRate, true)).ToString("G") : null, SuggestedPrice = child.ListPrice.HasValue ? Math.Round(PriceHelper.Convert(child.ListPrice.Value / 100, caExchangeRate, true)).ToString("G") : "", Currency = child.ListPrice.HasValue ? "CAD" : "", Quantity = child.RealQuantity.ToString("G"),// "1", // i.Quantity.ToString("G"), KeyProductFeatures1 = features.Count > 0 ? features[0] : String.Empty, KeyProductFeatures2 = features.Count > 1 ? features[1] : String.Empty, KeyProductFeatures3 = features.Count > 2 ? features[2] : String.Empty, KeyProductFeatures4 = features.Count > 3 ? features[3] : String.Empty, KeyProductFeatures5 = features.Count > 4 ? features[4] : String.Empty, RecommendedBrowseNodes1 = ExcelProductCAViewModel.GetRecommendedBrowseNodes1(newDepartment, child.Size, newItemType), MainImageURL = childImage, Parentage = "Child", ParentSKU = parentSku, RelationshipType = "Variation", VariationTheme = variationType, Department = newDepartment, Color = child.Color, Size = child.Size, SpecialSize = child.SpecialSize, }); } //Copy to parent child Type if (children.Count > 0) { models[0].Type = models[1].Type; } if (children.Count > 0 && useStyleImageMode == UseStyleImageModes.StyleImage) { models[0].MainImageURL = models[1].MainImageURL; } } filename = models[0].SKU + "_" + (subLicense != null ? subLicense.Value : "none") + "_CA.xls"; return(models); }
public static IList <ItemVariationExportViewModel> CreateStyleVariations(IUnitOfWork db, string styleString, IList <ItemSizeMapping> existSizeMapping, MarketType market, string marketplaceId) { var results = new List <ItemVariationExportViewModel>(); var style = db.Styles.GetActiveByStyleIdAsDto(styleString); if (style == null) { return(results); } var styleMainLicenseDto = db.StyleFeatureValues.GetFeatureValueByStyleIdByFeatureId(style.Id, StyleFeatureHelper.MAIN_LICENSE); var styleSubLicenseDto = db.StyleFeatureValues.GetFeatureValueByStyleIdByFeatureId(style.Id, StyleFeatureHelper.SUB_LICENSE1); var styleMainLicense = styleMainLicenseDto != null ? styleMainLicenseDto.Value : null; var styleSubLicense = styleSubLicenseDto != null ? styleSubLicenseDto.Value : null; var styleItems = db.StyleItems .GetByStyleIdWithBarcodesAsDto(style.Id) .OrderBy(o => SizeHelper.GetSizeIndex(o.Size)) .ToList(); var forceReplace = styleItems.Any(s => (s.Size ?? "").Contains("/")); foreach (var styleItem in styleItems) { var newItem = new ItemVariationExportViewModel(); var index = 0; var baseSKU = style.StyleID + "-" + SizeHelper.PrepareSizeForSKU(styleItem.Size, forceReplace); while (db.Listings.CheckForExistenceSKU(SkuHelper.SetSKUMiddleIndex(baseSKU, index), (MarketType)market, marketplaceId)) { index++; } newItem.IsSelected = true; newItem.SKU = SkuHelper.SetSKUMiddleIndex(baseSKU, index); newItem.StyleId = styleItem.StyleId; newItem.StyleString = style.StyleID; newItem.StyleItemId = styleItem.StyleItemId; newItem.StyleColor = styleItem.Color; newItem.StyleSize = styleItem.Size; newItem.BrandName = ItemExportHelper.GetBrandName(styleMainLicense, styleSubLicense); newItem.Size = SizeHelper.PrepareSizeForExport(db, newItem.StyleSize, existSizeMapping); if (styleItem.Barcodes != null) { foreach (var barcode in styleItem.Barcodes) { if (!String.IsNullOrEmpty(barcode.Barcode) && !db.Items.CheckForExistenceBarcode(barcode.Barcode, market, marketplaceId)) { newItem.Barcode = barcode.Barcode; break; } } } if (String.IsNullOrEmpty(newItem.Barcode)) { newItem.AutoGeneratedBarcode = true; } results.Add(newItem); } return(results); }
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); }
public static IList <ExcelProductUKViewModel> GetItemsFor(ILogService log, ITime time, IMarketCategoryService categoryService, IHtmlScraperService htmlScraper, IMarketApi marketApi, IUnitOfWork db, CompanyDTO company, string asin, MarketType market, string marketplaceId, UseStyleImageModes useStyleImageMode, out string filename) { var gbpExchangeRate = PriceHelper.GBPtoUSD; var today = time.GetAppNowTime(); var models = new List <ExcelProductUKViewModel>(); var items = ItemExportHelper.GetParentItemAndChilds(log, time, htmlScraper, marketApi, db, company, asin, null, ExportToExcelMode.Normal, market, marketplaceId); var parent = items.Item1; // db.ParentItems.GetAsDTO(asin); var children = items.Item2; // db.Items.GetAllActualExAsDto().Where(i => i.ParentASIN == asin).ToList(); var firstChild = children.FirstOrDefault(); FeatureValueDTO subLicense = null; string material = null; string materialComposition = null; string itemStyle = null; if (firstChild != null && firstChild.StyleId.HasValue) { subLicense = db.FeatureValues.GetValueByStyleAndFeatureId(firstChild.StyleId.Value, StyleFeatureHelper.SUB_LICENSE1); itemStyle = ItemExportHelper.GetFeatureValue(db.FeatureValues.GetValueByStyleAndFeatureId(firstChild.StyleId.Value, StyleFeatureHelper.ITEMSTYLE)); material = ItemExportHelper.GetFeatureValue(db.FeatureValues.GetValueByStyleAndFeatureId(firstChild.StyleId.Value, StyleFeatureHelper.MATERIAL)); materialComposition = ItemExportHelper.GetFeatureValue(db.FeatureValues.GetValueByStyleAndFeatureId(firstChild.StyleId.Value, StyleFeatureHelper.MATERIAL_COMPOSITION)); } if (parent != null) { var variationType = ItemExportHelper.GetVariationType(children != null ? children.Select(ch => ch.Color).ToList() : null); string childBrandName = null; decimal?childListPrice = null; string childDepartment = null; string childSize = null; string childStyleString = null; IList <string> childFeatures = new List <string>(); //IList<string> searchTermList = new List<string>(); //if (!String.IsNullOrEmpty(parent.SearchKeywords)) // searchTermList = parent.SearchKeywords.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList(); var searchTerms = (parent.SearchKeywords ?? "").Replace(";", ", "); searchTerms = ItemExportHelper.PrepareSearchTerms(searchTerms); if (firstChild != null) { childBrandName = children.Select(c => c.BrandName).FirstOrDefault(); childFeatures = !String.IsNullOrEmpty(firstChild.Features) ? firstChild.Features.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList() : new List <string>(); childListPrice = firstChild.ListPrice; childDepartment = firstChild.Department; childSize = firstChild.Size; childStyleString = firstChild.StyleString; } //For simplicity, w can just use for parent SKU (unless it exist) style-%parentASIN%. string parentSku = parent.SKU; if (String.IsNullOrEmpty(parentSku)) { parentSku = childStyleString + "-" + parent.ASIN; } var gender = childDepartment ?? parent.Department ?? ""; var sizeType = ItemExportHelper.GetSizeType(childSize); var categoryInfo = categoryService.GetCategory(market, marketplaceId, itemStyle, gender, sizeType); //var itemType = ItemExportHelper.GetItemType(itemStyle); var newItemType = categoryInfo.Key1;// ItemExportHelper.ItemTypeConverter(childSize ?? "", itemType, itemStyle, gender); var newDepartment = StringHelper.GetFirstNotEmpty(categoryInfo.Key2, ItemExportHelper.DepartmentConverter(gender, newItemType, sizeType)); string clothingType = categoryInfo.Key3; var parentImage = ItemExportHelper.ImageConverter(StringHelper.GetFirstNotEmpty( parent.LargeImage != null ? parent.LargeImage.Image : null, parent.ImageSource)); var replaces = new Dictionary <string, string>() { { "Nightgown", "Nighty" }, { "Nightys", "Nighties" }, { "nightgown", "nighty" }, { "nighty", "nighties" }, { "pajama", "pyjama" }, { "Pajama", "Pyjama" }, }; var hasCotton = StringHelper.GetInOneOfStrings("Cotton", new List <string>() { searchTerms, parent.AmazonName, parent.Description, firstChild != null ? firstChild.Features : null }); if (String.IsNullOrEmpty(materialComposition)) { if (hasCotton) { materialComposition = "Cotton"; } else { materialComposition = "Polyester"; } } models.Add(new ExcelProductUKViewModel { SKU = parentSku, Title = StringHelper.Replaces(parent.AmazonName, replaces), ASIN = parent.ASIN, UPC = "", ProductId = "",// "ASIN", BrandName = parent.BrandName ?? childBrandName, Description = parent.Description, ClothingType = clothingType, Update = "Update", StandardPrice = "", //SuggestedPrice = childListPrice.HasValue ? PriceHelper.Convert(childListPrice.Value / 100, gbpExchangeRate, false).ToString("G") : "", Currency = "", // childListPrice.HasValue ? "GBP" : "", Quantity = "", RecommendedBrowseNodes1 = ExcelProductUKViewModel.GetRecommendedBrowseNodes1(newDepartment, childSize, newItemType), KeyProductFeatures1 = childFeatures.Count > 0 ? childFeatures[0] : String.Empty, KeyProductFeatures2 = childFeatures.Count > 1 ? childFeatures[1] : String.Empty, KeyProductFeatures3 = childFeatures.Count > 2 ? childFeatures[2] : String.Empty, KeyProductFeatures4 = childFeatures.Count > 3 ? childFeatures[3] : String.Empty, KeyProductFeatures5 = childFeatures.Count > 4 ? childFeatures[4] : String.Empty, SearchTerms1 = searchTerms, //SearchTerms2 = searchTermList.Count > 1 ? searchTermList[1] : String.Empty, //SearchTerms3 = searchTermList.Count > 2 ? searchTermList[2] : String.Empty, //SearchTerms4 = searchTermList.Count > 3 ? searchTermList[3] : String.Empty, //SearchTerms5 = searchTermList.Count > 4 ? searchTermList[4] : String.Empty, MainImageURL = parentImage, Parentage = "Parent", VariationTheme = variationType, MaterialComposition = materialComposition, Department = newDepartment, Color = "", }); foreach (var child in children) { var styleImages = child.StyleId.HasValue ? db.StyleImages .GetAllAsDto() .Where(im => im.StyleId == child.StyleId.Value && im.IsDefault) .ToList() : null; IList <string> features = !String.IsNullOrEmpty(child.Features) ? child.Features.Split(";".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList() : new List <string>(); var childImage = ItemExportHelper.ImageConverter(StringHelper.GetFirstNotEmpty( child.LargeImageUrl, child.ImageUrl)); if (useStyleImageMode == UseStyleImageModes.StyleImage) { if (styleImages != null && styleImages.Any()) { childImage = styleImages.First().Image; } } var itemPrice = child.SalePrice.HasValue && child.SaleStartDate < today ? child.SalePrice.Value : child.CurrentPrice; models.Add(new ExcelProductUKViewModel { StyleId = child.StyleId, StyleItemId = child.StyleItemId, SKU = child.SKU, Title = StringHelper.Replaces(child.Name, replaces), ASIN = child.ASIN, UPC = child.Barcode, ProductId = "UPC",// "ASIN", BrandName = child.BrandName, Description = parent.Description, ClothingType = clothingType, Update = "Update", StandardPrice = CorrectUKPrice(PriceHelper.Convert(itemPrice, gbpExchangeRate, true)).ToString("G"), //SuggestedPrice = child.ListPrice.HasValue ? PriceHelper.Convert(child.ListPrice.Value / 100, gbpExchangeRate, false).ToString("G") : "", Currency = "GBP", Quantity = child.RealQuantity.ToString("G"),// "1", // i.Quantity.ToString("G"), KeyProductFeatures1 = features.Count > 0 ? features[0] : String.Empty, KeyProductFeatures2 = features.Count > 1 ? features[1] : String.Empty, KeyProductFeatures3 = features.Count > 2 ? features[2] : String.Empty, KeyProductFeatures4 = features.Count > 3 ? features[3] : String.Empty, KeyProductFeatures5 = features.Count > 4 ? features[4] : String.Empty, SearchTerms1 = searchTerms, //SearchTerms2 = searchTermList.Count > 1 ? searchTermList[1] : String.Empty, //SearchTerms3 = searchTermList.Count > 2 ? searchTermList[2] : String.Empty, //SearchTerms4 = searchTermList.Count > 3 ? searchTermList[3] : String.Empty, //SearchTerms5 = searchTermList.Count > 4 ? searchTermList[4] : String.Empty, RecommendedBrowseNodes1 = ExcelProductUKViewModel.GetRecommendedBrowseNodes1(newDepartment, childSize, newItemType), MainImageURL = childImage, Parentage = "Child", ParentSKU = parentSku, RelationshipType = "Variation", VariationTheme = variationType, Department = newDepartment, Color = child.Color, MaterialComposition = materialComposition, Size = ItemExportHelper.PrepareSize(ExcelProductUKViewModel.SizeConverter(child.Size, material), gender), }); } if (children.Count > 0 && useStyleImageMode == UseStyleImageModes.StyleImage) { models[0].MainImageURL = models[1].MainImageURL; } } filename = models[0].SKU + "_" + (subLicense != null ? subLicense.Value : "none") + "_UK.xls"; return(models); }
public static IList <ExcelProductWalmartViewModel> GenerateToExcelWalmart(IUnitOfWork db, IBarcodeService barcodeService, StyleViewModel model, DateTime when, out string filename) { var models = new List <ExcelProductWalmartViewModel>(); var childs = new List <ExcelProductWalmartViewModel>(); 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, Feature.SHIPPING_SIZE); //var itemType = ItemExportHelper.GetItemType(itemStyle); var brandName = ItemExportHelper.GetBrandName(mainLicense, subLicense); //var newItemType = ItemExportHelper.ItemTypeConverter(firstSize ?? "", itemType, itemStyle, gender); //var newDepartment = ItemExportHelper.DepartmentConverter(newItemType, gender, firstSize); var searchTerms = model.SearchTerms; // ItemExportHelper.BuildSearchTerms(itemStyle, material, sleeve); var keyFeatures = model.GetBulletPoints(); // ItemExportHelper.BuildKeyFeatures(mainLicense, subLicense, material); ////-------------------------- ////Parent item ////-------------------------- //parent.SKU = model.StyleId; //parent.ASIN = ""; //parent.Title = model.Name // + (!String.IsNullOrEmpty(sizeGroupName) ? ", " + ItemExportHelper.FormatSizeGroupName(sizeGroupName) : String.Empty) // + " " + sizeRange; //parent.ProductId = ""; //parent.BrandName = brandName; //parent.Type = newItemType; //if (model.ImageSet != null) //{ // parent.MainImageURL = model.ImageSet.Image1Url; // parent.OtherImageUrl1 = model.ImageSet.Image2Url; // parent.OtherImageUrl2 = model.ImageSet.Image3Url; // parent.OtherImageUrl3 = model.ImageSet.Image4Url; //} //parent.Color = "";// color1; //parent.Department = newDepartment; //parent.Size = ""; //parent.Description = model.Description; //parent.Update = "Update"; //parent.StandardPrice = ""; //parent.SuggestedPrice = model.MSRP.ToString("G"); //parent.Currency = "USD"; //parent.Quantity = ""; //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.SearchTerms1 = searchTerms; ////parent.SearchTerms3 = ""; ////parent.SearchTerms4 = ""; ////parent.SearchTerms5 = ""; //parent.Parentage = "Parent"; //parent.ParentSKU = ""; //parent.RelationshipType = ""; //parent.VariationTheme = "Size"; //-------------------------- //Child items //-------------------------- foreach (var size in sizes) { var child = new ExcelProductWalmartViewModel(); 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.ProductIdType = "UPC"; child.ProductId = (size.Barcodes != null && size.Barcodes.Any()) ? size.Barcodes.FirstOrDefault().Barcode : String.Empty; child.Title = model.Name + ", " + ItemExportHelper.FormatSizeGroupName(size.SizeGroupName) + " Size " + ItemExportHelper.ConvertSizeForItemName(size.Size, hasKids2); child.LongDescription = String.IsNullOrEmpty(model.Description) ? child.Title : model.Description; child.ShortDescription = String.IsNullOrEmpty(model.Description) ? child.Title : model.Description; child.ShelfDescription = String.IsNullOrEmpty(model.Description) ? child.Title : model.Description; child.Brand = brandName; if (model.ImageSet != null) { child.MainImageUrl = model.ImageSet.Image1Url; //child.OtherImageUrl1 = model.ImageSet.Image2Url; //child.OtherImageUrl2 = model.ImageSet.Image3Url; //child.OtherImageUrl3 = model.ImageSet.Image4Url; } child.MainImageAltText = child.Title; child.Color = ItemExportHelper.PrepareColor(String.IsNullOrEmpty(size.Color) ? color1 : size.Color); child.ClothingSize = size.Size; child.PriceCurrency = "USD"; child.PriceAmount = 99.99M; //TODO: price child.MinAdvertisedPriceAmount = child.PriceAmount; child.MinAdvertisedPriceCurrency = child.PriceCurrency; child.VariantGroupId = model.StyleId; child.VariantAttributeNames = "ClothingSize"; //TODO: add optional Color child.ProductTaxCode = "2038345"; child.ShippingWeightValue = size.Weight.ToString(); child.ShippingWeightUnit = "OZ"; childs.Add(child); } models.AddRange(childs); filename = model.StyleId + "_" + subLicense; filename = filename.Replace(" ", "") + "_Walmart" + Path.GetExtension(TemplatePath); return(models); }
public void FindListingsPositionInSearch() { var results = new List <StylePositionOnWM>(); using (var db = _dbFactory.GetRWDb()) { var openApi = new WalmartOpenApi(_log, "trn9fdghvb8p9gjj9j6bvjwx"); var wmItems = db.Items.GetAll().Where(i => i.ItemPublishedStatus == (int)PublishedStatuses.Published && i.Market == (int)MarketType.Walmart).ToList(); var styleIdList = wmItems.Where(i => i.StyleId.HasValue).Select(i => i.StyleId.Value).ToList(); //var styleList = db.Styles.GetAll().Where(st => styleIdList.Contains(st.Id)).ToList(); var groupByStyle = from siCache in db.StyleItemCaches.GetAll() group siCache by siCache.StyleId into byStyle select new { StyleId = byStyle.Key, OneHasStrongQty = byStyle.Any(s => s.RemainingQuantity > 20), //NOTE: >20 Qty = byStyle.Sum(s => s.RemainingQuantity) }; var styleList = (from s in db.Styles.GetAll() join sCache in db.StyleCaches.GetAll() on s.Id equals sCache.Id join qty in groupByStyle on s.Id equals qty.StyleId where styleIdList.Contains(s.Id) orderby qty.Qty descending select s) .Take(100) .ToList(); var mainLicenseFeatures = db.StyleFeatureValues.GetFeatureValueByStyleIdByFeatureId(styleIdList, new[] { StyleFeatureHelper.MAIN_LICENSE }).ToList(); var subLicenseFeatures = db.StyleFeatureValues.GetFeatureValueByStyleIdByFeatureId(styleIdList, new[] { StyleFeatureHelper.SUB_LICENSE1 }).ToList(); var itemStyleFeatures = db.StyleFeatureValues.GetFeatureValueByStyleIdByFeatureId(styleIdList, new[] { StyleFeatureHelper.ITEMSTYLE }).ToList(); var resultsCache = new Dictionary <string, IList <OpenItem> >(); foreach (var style in styleList) { var mainLicenseFeature = mainLicenseFeatures.FirstOrDefault(f => f.StyleId == style.Id); var subLicenseFeature = subLicenseFeatures.FirstOrDefault(f => f.StyleId == style.Id); var itemStyleFeature = itemStyleFeatures.FirstOrDefault(f => f.StyleId == style.Id); var brandName = ItemExportHelper.GetBrandName(mainLicenseFeature?.Value, subLicenseFeature?.Value); var manufacture = brandName; if (String.IsNullOrEmpty(manufacture)) { manufacture = style.Manufacturer; } var itemStyle = itemStyleFeature?.Value ?? "Pajamas"; itemStyle = itemStyle.Replace("– 2pc", "").Replace("– 3pc", "").Replace("– 4pc", "").Trim(); var keywords = StringHelper.JoinTwo(" ", manufacture, itemStyle); _log.Info("Searching: " + keywords); IList <OpenItem> foundItems = new List <OpenItem>(); if (resultsCache.ContainsKey(keywords)) { foundItems = resultsCache[keywords]; } else { var searchResult = openApi.SearchProducts(keywords, WalmartUtils.ApparelCategoryId, null, null, 1, 100); if (searchResult.IsSuccess) { foundItems = searchResult.Data; resultsCache.Add(keywords, searchResult.Data); } } int? position = null; string positionItemId = null; string buyBoxWinner = null; var itemsByStyle = wmItems.Where(i => i.StyleId == style.Id).ToList(); var itemIds = itemsByStyle.Where(i => !String.IsNullOrEmpty(i.SourceMarketId)).Select(i => i.SourceMarketId).ToList(); for (int i = 0; i < foundItems.Count; i++) { if (itemIds.Contains(foundItems[i].ItemId) && position == null) { position = i + 1; positionItemId = foundItems[i].ItemId; buyBoxWinner = foundItems[i].SellerInfo; } } _log.Info(style.StyleID + ", position: " + position + ", " + keywords + ", " + buyBoxWinner + ", " + positionItemId); results.Add(new StylePositionOnWM() { StyleId = style.Id, StyleString = style.StyleID, SearchKeywords = keywords, RequestedSearchResults = foundItems.Count, BestPosition = position, BuyBoxWinner = buyBoxWinner, BestPositionUrl = !String.IsNullOrEmpty(positionItemId) ? String.Format("https://www.walmart.com/ip/item/{0}", positionItemId) : "" }); } } var filename = "WMListingPositions_" + DateTime.Now.ToString("MM_dd_yyyy_hh_mm_ss") + ".xls"; var filepath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, filename); var b = new ExportColumnBuilder <StylePositionOnWM>(); var columns = new List <ExcelColumnInfo>() { b.Build(p => p.StyleString, "Style Id", 25), b.Build(p => p.SearchKeywords, "Search Keywords", 25), b.Build(p => p.RequestedSearchResults, "Requested search results", 35), b.Build(p => p.BestPosition, "Best Position", 15), b.Build(p => p.BuyBoxWinner, "Buy Box Winner", 20), b.Build(p => p.BestPositionUrl, "Best Position Url", 45) }; using (var stream = ExcelHelper.Export(results, columns)) { stream.Seek(0, SeekOrigin.Begin); using (var fileStream = File.Create(filepath)) { stream.CopyTo(fileStream); } } }