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);
}
}
}
