public void ExportMissingItem(IList <ItemDTO> items, string filepath)
{
var b = new ExportColumnBuilder <ItemDTO>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.StyleString, "Style Id", 20),
b.Build(p => p.StyleSize, "Style Size", 15),
b.Build(p => p.RemainingQuantity, "Remaining Qty", 20),
b.Build(p => p.ParentASIN, "ParentASIN", 15),
b.Build(p => p.ASIN, "ASIN", 15),
b.Build(p => p.Size, "Amazon Size", 15),
b.Build(p => p.Color, "Amazon Color", 15),
b.Build(p => p.SourceMarketUrl, "Amazon Url", 40),
};
using (var stream = ExcelHelper.Export(items, columns))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(filepath))
{
stream.CopyTo(fileStream);
}
}
}
public void ExportBargains(IList <BargainItem> bargainList, string filepath)
{
var b = new ExportColumnBuilder <BargainItemExport>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.Barcode, "Barcode", 15),
b.Build(p => p.Size, "Size", 15),
b.Build(p => p.Color, "Color", 15),
b.Build(p => p.WalmartInStock, "Walmart InStock", 15),
b.Build(p => p.WalmartPrice, "Walmart Price", 15),
b.Build(p => p.WalmartItemId, "ItemId", 15),
b.Build(p => p.WalmartItemUrl, "Walmart Url", 15),
b.Build(p => p.AmazonInStock, "Amazon InStock", 15),
b.Build(p => p.AmazonPrice, "Amazon Price", 15),
b.Build(p => p.AmazonASIN, "ASIN", 15),
b.Build(p => p.AmazonUrl, "Amazon Url", 15),
};
//var filename = "BargainsSearch_" + DateTime.Now.ToString("MM_dd_yyyy_hh_mm_ss") + ".xls";
//var filepath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, filename);
var items = bargainList.Select(bi => new BargainItemExport(bi)).ToList();
using (var stream = ExcelHelper.Export(items, columns))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(filepath))
{
stream.CopyTo(fileStream);
}
}
}
public void ExportFoundItem(IList <FoundItemInfo> items, string filepath)
{
var b = new ExportColumnBuilder <FoundItemInfo>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.Barcode, "Barcode", 15),
b.Build(p => p.StyleString, "Style Id", 20),
b.Build(p => p.StyleSize, "Style Size", 15),
b.Build(p => p.RemainingQuantity, "Remaining Qty", 20),
b.Build(p => p.Name, "Name", 50),
b.Build(p => p.ASIN, "ASIN", 15),
b.Build(p => p.AmazonSize, "Amazon Size", 15),
b.Build(p => p.AmazonColor, "Amazon Color", 15),
b.Build(p => p.AmazonUrl, "Amazon Url", 40),
};
using (var stream = ExcelHelper.Export(items, columns))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(filepath))
{
stream.CopyTo(fileStream);
}
}
}
public static MemoryStream ExportToExcel(ILogService log,
ITime time,
IUnitOfWork db,
ShipmentReportSearchFilterViewModel filter,
bool isFulfilment)
{
//string templateName = AppSettings.OrderReportTemplate;
var gridItems = GetItems(db, filter);
var b = new ExportColumnBuilder <ShipmentReportViewModel>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.ShippingDate, "Shipping Date", 25),
b.Build(p => p.FormattedOrderId, "Order Id", 25),
b.Build(p => p.MarketName, "Market", 25),
b.Build(p => p.TotalOrderedQty, "Ordered Quantity", 15),
b.Build(p => p.TotalUpchargedShippingCost, "Shipping Cost", 20)
};
if (isFulfilment)
{
columns.Add(b.Build(p => p.TotalShippingCost, "Shipping Cost (w/o upcharge)", 20));
}
return(ExcelHelper.Export(gridItems.Items,
columns));
}
public static MemoryStream BuildReport(IList <DhlInvoiceViewModel> invoices)
{
var b = new ExportColumnBuilder <DhlInvoiceViewModel>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.InvoiceNumber, "Invoice #", 15),
b.Build(p => p.InvoiceDate, "Invoice Date", 15),
b.Build(p => p.BillNumber, "Airbill", 15),
b.Build(p => p.WeightUsed, "Total Weight ", 15),
b.Build(p => p.Estimated, "Estimated", 15),
b.Build(p => p.Charged, "Charged", 15),
b.Build(p => p.OrderDate, "Order Date", 15),
b.Build(p => p.OrderNumber, "Order #", 21),
};
return(ExcelHelper.Export(invoices.OrderBy(i => i.InvoiceNumber).ToList(), columns));
}
static void Main(string[] args)
{
var b = new ExportColumnBuilder <TestImage>();
var filename = "Test_" + DateTime.Now.ToString("MM_dd_yyyy_hh_mm_ss") + ".xls";
var filepath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, filename);
var items = new List <TestImage>()
{
new TestImage {
CategoryID = "test", ImagePath = "https://images.sftcdn.net/images/t_app-cover-l,f_auto/p/befbcde0-9b36-11e6-95b9-00163ed833e7/260663710/the-test-fun-for-friends-screenshot.jpg"
}
};
using (var stream = ExcelHelper.Export(items, null))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(filepath))
{
stream.CopyTo(fileStream);
}
}
}
public void GenerateProductFeed()
{
var filename = String.Format("product-feed-{0}.xls", _time.GetAppNowTime().ToString("yyyyMMddHHmmss"));
var outputFilepath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, filename);
IList <FtpMarketProductItem> items;
IList <StyleFeatureValueDTO> allValueFeatures;
IList <StyleFeatureValueDTO> allTextFeatures;
IList <StyleImageDTO> allImages;
using (var db = _dbFactory.GetRWDb())
{
items = (from st in db.Styles.GetAll()
join si in db.StyleItems.GetAll() on st.Id equals si.StyleId
join sic in db.StyleItemCaches.GetAll() on si.Id equals sic.Id
join i in db.Items.GetAll() on si.Id equals i.StyleItemId
join l in db.Listings.GetAll() on i.Id equals l.ItemId
where i.Market == (int)MarketType.FtpMarket &&
!l.IsRemoved &&
sic.RemainingQuantity > 0 &&
i.StyleId.HasValue &&
i.StyleItemId.HasValue
select new FtpMarketProductItem()
{
StyleId = i.StyleId.Value,
StyleItemId = i.StyleItemId.Value,
StyleString = st.StyleID,
SKU = l.SKU,
Barcode = i.Barcode,
VariationGroupId = i.ParentASIN,
Size = i.Size,
Color = i.Color,
Price = l.CurrentPrice,
Quantity = sic.RemainingQuantity,
Name = st.Name,
BrandName = st.Manufacturer,
Description = st.Description,
BulletPoint1 = st.BulletPoint1,
BulletPoint2 = st.BulletPoint2,
BulletPoint3 = st.BulletPoint3,
BulletPoint4 = st.BulletPoint4,
BulletPoint5 = st.BulletPoint5,
SearchTerms = st.SearchTerms
}).ToList();
items = items.OrderBy(i => i.StyleString)
.ThenBy(i => i.StyleId)
.ThenBy(i => SizeHelper.GetSizeIndex(i.Size))
.ThenBy(i => i.Color)
.ToList();
var styleIdList = items.Select(i => i.StyleId).Distinct().ToList();
allValueFeatures = db.StyleFeatureValues.GetAllWithFeature().Where(f => styleIdList.Contains(f.StyleId)).ToList();
allTextFeatures = db.StyleFeatureTextValues.GetAllWithFeature().Where(f => styleIdList.Contains(f.StyleId)).ToList();
allImages = db.StyleImages.GetAllAsDto()
.Where(i => styleIdList.Contains(i.StyleId) &&
!i.IsSystem)
.OrderBy(im => im.Id)
.ToList();
}
foreach (var item in items)
{
var styleId = item.StyleId;
var images = allImages.Where(i => i.StyleId == item.StyleId).ToList();
if (images.Count > 0)
{
item.MainImage = images[0].Image;
}
if (images.Count > 1)
{
item.AdditionalImage1 = images[1].Image;
}
if (images.Count > 2)
{
item.AdditionalImage2 = images[2].Image;
}
if (images.Count > 3)
{
item.AdditionalImage3 = images[3].Image;
}
if (images.Count > 4)
{
item.AdditionalImage4 = images[4].Image;
}
if (images.Count > 5)
{
item.AdditionalImage5 = images[5].Image;
}
var valueFeatures = allValueFeatures.Where(i => i.StyleId == item.StyleId).ToList();
var textFeatures = allTextFeatures.Where(i => i.StyleId == item.StyleId).ToList();
item.Gender = valueFeatures.FirstOrDefault(f => f.FeatureName == "Gender")?.Value;
item.ItemStyle = valueFeatures.FirstOrDefault(f => f.FeatureName == "Item style")?.Value;
item.Accessories = valueFeatures.FirstOrDefault(f => f.FeatureName == "Accessories")?.Value;
item.Sleeve = valueFeatures.FirstOrDefault(f => f.FeatureName == "Sleeve")?.Value;
item.Season = valueFeatures.FirstOrDefault(f => f.FeatureName == "Season")?.Value;
item.Material = valueFeatures.FirstOrDefault(f => f.FeatureName == "Material")?.Value;
item.MaterialComposition = textFeatures.FirstOrDefault(f => f.FeatureName == "Material Composition")?.Value;
item.Color1 = valueFeatures.FirstOrDefault(f => f.FeatureName == "Color1")?.Value;
item.Color2 = valueFeatures.FirstOrDefault(f => f.FeatureName == "Color2")?.Value;
item.MainLicense = valueFeatures.FirstOrDefault(f => f.FeatureName == "Main License")?.Value;
item.SubLicense = valueFeatures.FirstOrDefault(f => f.FeatureName == "Sub License")?.Value;
item.SecondarySubLicense = valueFeatures.FirstOrDefault(f => f.FeatureName == "Secondary Sub License")?.Value;
item.Holiday = valueFeatures.FirstOrDefault(f => f.FeatureName == "Holiday")?.Value;
item.Occasion = valueFeatures.FirstOrDefault(f => f.FeatureName == "Occasion")?.Value;
}
var b = new ExportColumnBuilder <FtpMarketProductItem>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.SKU, "SKU", 15),
b.Build(p => p.Barcode, "Barcode", 15),
b.Build(p => p.VariationGroupId, "VariationGroupId", 15),
b.Build(p => p.Size, "VariationSize", 15),
b.Build(p => p.Color, "VariationColor", 15),
b.Build(p => p.Price, "Price", 15),
b.Build(p => p.Quantity, "Quantity", 15),
b.Build(p => p.Name, "Name", 15),
b.Build(p => p.MainImage, "MainImage", 15),
b.Build(p => p.AdditionalImage1, "AdditionalImage1", 15),
b.Build(p => p.AdditionalImage2, "AdditionalImage2", 15),
b.Build(p => p.AdditionalImage3, "AdditionalImage3", 15),
b.Build(p => p.AdditionalImage4, "AdditionalImage4", 15),
b.Build(p => p.AdditionalImage5, "AdditionalImage5", 15),
b.Build(p => p.Description, "Description", 15),
b.Build(p => p.BulletPoint1, "BulletPoint1", 15),
b.Build(p => p.BulletPoint2, "BulletPoint2", 15),
b.Build(p => p.BulletPoint3, "BulletPoint3", 15),
b.Build(p => p.BulletPoint4, "BulletPoint4", 15),
b.Build(p => p.BulletPoint5, "BulletPoint5", 15),
b.Build(p => p.SearchTerms, "SearchTerms", 15),
b.Build(p => p.Gender, "Gender", 15),
b.Build(p => p.ItemStyle, "ItemStyle", 15),
b.Build(p => p.Accessories, "Accessories", 15),
b.Build(p => p.Sleeve, "Sleeve", 15),
b.Build(p => p.Season, "Season", 15),
b.Build(p => p.Material, "Material", 15),
b.Build(p => p.MaterialComposition, "MaterialComposition", 15),
b.Build(p => p.Color1, "Color1", 15),
b.Build(p => p.Color2, "Color2", 15),
b.Build(p => p.MainLicense, "MainLicense", 15),
b.Build(p => p.SubLicense, "SubLicense", 15),
b.Build(p => p.SecondarySubLicense, "SecondarySubLicense", 15),
b.Build(p => p.Holiday, "Holiday", 15),
b.Build(p => p.Occasion, "Occasion", 15),
};
using (var stream = ExcelHelper.Export(items, columns))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(outputFilepath))
{
stream.CopyTo(fileStream);
}
}
}
public void ChangeFormat(string inputFilepath, string outputFilepath)
{
var sourceItems = new List <RecordInfo>();
using (var stream = new FileStream(inputFilepath, FileMode.Open, FileAccess.ReadWrite))
{
IWorkbook workbook = null;
if (inputFilepath.EndsWith(".xlsx"))
{
workbook = new XSSFWorkbook(stream);
}
else
{
workbook = new HSSFWorkbook(stream);
}
var sheet = workbook.GetSheetAt(0);
var lastCTN = "";
for (var i = 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
if (row.GetCell(2) == null)
{
continue;
}
var newRecord = new RecordInfo()
{
CTN = row.GetCell(0) != null?row.GetCell(0).ToString() : lastCTN,
Style = row.GetCell(2).ToString(),
Color = row.GetCell(3).ToString(),
Size = row.GetCell(4).ToString(),
UnitPrice = row.GetCell(5).ToString(),
TotalQty = Int32.Parse(row.GetCell(6).ToString()),
Barcode = row.GetCell(8).ToString()
};
sourceItems.Add(newRecord);
lastCTN = newRecord.CTN;
}
}
var resultItems = sourceItems.GroupBy(r => new { r.Style, r.Size, r.Color, r.Barcode })
.Select(r => new RecordInfo()
{
Style = r.Key.Style,
Size = r.Key.Size,
Color = r.Key.Color,
Barcode = r.Key.Barcode,
UnitPrice = r.Max(i => i.UnitPrice),
TotalQty = r.Sum(i => i.TotalQty),
CTN = string.Join(", ", r.Select(i => i.CTN).Where(i => !String.IsNullOrEmpty(i)).OrderBy(i => Int32.Parse(i)).Distinct())
}).ToList();
var b = new ExportColumnBuilder <RecordInfo>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.Style, "Style", 15),
b.Build(p => p.Color, "Color", 15),
b.Build(p => p.Size, "Size", 15),
b.Build(p => p.TotalQty, "Total qty", 15),
b.Build(p => p.UnitPrice, "Unit Price", 15),
b.Build(p => p.Barcode, "Barcode", 15),
b.Build(p => p.CTN, "Boxes", 15),
};
using (var stream = ExcelHelper.Export(resultItems, columns))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(outputFilepath))
{
stream.CopyTo(fileStream);
}
}
}
public void BuildFullInventoryReport()
{
using (var db = _dbFactory.GetRWDb())
{
var allStyles = db.Styles.GetAll().Where(st => !st.Deleted)
.OrderBy(st => st.StyleID)
.ToList();
var allStyleItemCaches = db.StyleItemCaches.GetAll().ToList();
var allLocations = db.StyleLocations.GetAll().ToList();
var allOpenBoxes = db.OpenBoxes.GetAll().ToList();
var allSealedBoxes = db.SealedBoxes.GetAll().ToList();
var allOpenBoxItems = db.OpenBoxItems.GetAll().Where(ob => ob.StyleItemId.HasValue).ToList();
var allSealedBoxItems = db.SealedBoxItems.GetAll().Where(ob => ob.StyleItemId.HasValue).ToList();
var results = new List <InventoryRecord>();
foreach (var style in allStyles)
{
var remainingQty = allStyleItemCaches.Where(sic => sic.StyleId == style.Id).Sum(sic => Math.Max(0, sic.RemainingQuantity));
var locations = allLocations.Where(l => l.StyleId == style.Id).ToList();
var locationString = String.Join("; ",
locations.OrderByDescending(l => l.IsDefault)
.Select(l => l.Isle + "/" + l.Section + "/" + l.Shelf + (l.IsDefault ? "(def)" : "")));
decimal totalItemCost = 0;
var totalBoxCount = 0;
var openBoxes = allOpenBoxes.Where(ob => ob.StyleId == style.Id).Where(ob => !ob.Deleted && !ob.Archived).ToList();
foreach (var openBox in openBoxes)
{
if (openBox.Price == 0)
{
continue;
}
var qty = allOpenBoxItems.Where(ob => ob.BoxId == openBox.Id).Sum(ob => ob.Quantity);
totalItemCost += openBox.BoxQuantity * qty * openBox.Price;
totalBoxCount += openBox.BoxQuantity * qty;
}
var sealedBoxes = allSealedBoxes.Where(sb => sb.StyleId == style.Id).Where(ob => !ob.Deleted && !ob.Archived).ToList();
foreach (var sealedBox in sealedBoxes)
{
if (sealedBox.PajamaPrice == 0)
{
continue;
}
var qty = allSealedBoxItems.Where(sb => sb.BoxId == sealedBox.Id).Sum(sb => sb.BreakDown);
totalItemCost += sealedBox.BoxQuantity * qty * sealedBox.PajamaPrice;
totalBoxCount += sealedBox.BoxQuantity * qty;
}
var avgCost = totalBoxCount != 0 ? totalItemCost / (decimal)totalBoxCount : (decimal?)null;
if (avgCost == null || avgCost == 0)
{
avgCost = 5.01M;
}
results.Add(new InventoryRecord()
{
StyleId = style.StyleID,
Name = style.Name,
TotalQty = remainingQty,
Locations = locationString,
AvgCost = PriceHelper.RoundToTwoPrecision(avgCost),
});
}
_log.Info("Records: " + results.Count);
var filename = "InventoryOnAmazon_" + DateTime.Now.ToString("MM_dd_yyyy_hh_mm_ss") + ".xls";
var filepath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, filename);
var b = new ExportColumnBuilder <InventoryRecord>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.StyleId, "Style Id", 30),
b.Build(p => p.Name, "Name", 45),
b.Build(p => p.TotalQty, "Total Qty", 20),
b.Build(p => p.Locations, "Locations list", 30),
b.Build(p => p.AvgCost, "Item Cost", 15),
};
using (var stream = ExcelHelper.Export(results, columns))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(filepath))
{
stream.CopyTo(fileStream);
}
}
}
}
public void ExportSetupByMatch()
{
using (var db = _dbFactory.GetRWDb())
{
var allAmazonItems = db.Items.GetAllViewActual()
.Where(i => i.Market == (int)MarketType.Amazon &&
i.MarketplaceId == MarketplaceKeeper.AmazonComMarketplaceId)
.Select(i => new ItemDTO()
{
SKU = i.SKU,
Barcode = i.Barcode,
CurrentPrice = i.CurrentPrice,
StyleId = i.StyleId,
})
.ToList();
var allWalmartItems = db.Items.GetAllViewActual()
.Where(i => i.Market == (int)MarketType.Walmart)
.Select(i => new ItemDTO()
{
SKU = i.SKU,
Barcode = i.Barcode
})
.ToList();
var results = new List <SetupByMatchProduct>(allAmazonItems.Count);
foreach (var amzItem in allAmazonItems)
{
var genderValue = amzItem.StyleId.HasValue
? db.StyleFeatureValues.GetFeatureValueByStyleIdByFeatureId(amzItem.StyleId.Value, StyleFeatureHelper.GENDER)?.Value
: null;
if (allWalmartItems.All(i => i.Barcode != amzItem.Barcode))
{
var sku = amzItem.SKU;
var index = 0;
while (allWalmartItems.Any(i => i.SKU == sku))
{
sku = SkuHelper.SetSKUMiddleIndex(sku, index);
index++;
}
results.Add(new SetupByMatchProduct()
{
ProductIdType1 = "UPC",
ProductId1 = amzItem.Barcode,
ProductTaxCode = WalmartUtils.GetProductTaxCode(genderValue, null)?.ToString(),
SKU = sku,
Currency = "USD",
Price = amzItem.CurrentPrice,
Weight = PriceHelper.RoundToTwoPrecision((decimal)((amzItem.Weight == null || amzItem.Weight == 0) ? 5 : amzItem.Weight.Value) / (decimal)16),
WeightUnit = "lb",
Category = "Clothing",
SubCategory = "Clothing"
});
}
}
var b = new ExportColumnBuilder <SetupByMatchProduct>();
var columns = new List <ExcelColumnInfo>()
{
b.Build(p => p.ProductIdType1, "Product Identifier-Product Id Type (#1) *", 15),
b.Build(p => p.ProductId1, "Product Identifier-Product Id (#1) *", 15),
b.Build(p => p.ProductTaxCode, "Product Tax Code *", 15),
b.Build(p => p.ProductIdType2, "Additional Product Attribute-Product Attribute Name (#1) (Optional)", 15),
b.Build(p => p.ProductId2, "Additional Product Attribute-Product Attribute Value (#1) (Optional)", 15),
b.Build(p => p.SKU, "Sku *", 15),
b.Build(p => p.ASIN, "ASIN (Optional)", 15),
b.Build(p => p.Currency, "Price-Currency *", 15),
b.Build(p => p.Price, "Price-Amount *", 15),
b.Build(p => p.Weight, "Shipping Weight-Value *", 15),
b.Build(p => p.WeightUnit, "Shipping Weight-Unit *", 15),
b.Build(p => p.Category, "Category *", 15),
b.Build(p => p.SubCategory, "Sub-category *", 15),
};
var outputFilepath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "WMSetupByMatch-" + DateTime.Now.ToString("yyyyddMMHHmmss") + ".xls");
using (var stream = ExcelHelper.Export(results,
columns,
null))
{
stream.Seek(0, SeekOrigin.Begin);
using (var fileStream = File.Create(outputFilepath))
{
stream.CopyTo(fileStream);
}
}
}
}
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);
}
}
}
