예제 #1
0
        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);
                }
            }
        }
예제 #3
0
        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));
        }
예제 #5
0
        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));
        }
예제 #6
0
        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);
                }
            }
        }
예제 #9
0
        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);
                    }
                }
            }
        }
예제 #10
0
        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);
                }
            }
        }