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