public virtual ActionResult ExportToExcelUS(string asin, MarketType market, string marketplaceId, bool useStyleImage) { LogI("ExportToExcelUS, asin=" + asin + ", market=" + market + ", marketplaceId=" + marketplaceId + ", useStyleImage=" + useStyleImage); var marketplaceManager = new MarketplaceKeeper(DbFactory, false); marketplaceManager.Init(); IMarketApi api = new MarketFactory(marketplaceManager.GetAll(), Time, LogService, DbFactory, null) .GetApi(AccessManager.Company.Id, market, marketplaceId); string filename; var output = ExcelProductUSViewModel.ExportToExcelUS(LogService, Time, AmazonCategoryService, HtmlScraper, api, Db, AccessManager.Company, asin, market, marketplaceId, useStyleImage, out filename); return(File(output.ToArray(), //The binary data of the XLS file "application/vnd.ms-excel", //MIME type of Excel files //string.Format("{0}s_{1}.xls", asin, DateTime.Now.ToString(DateHelper.DateTimeFormat)) filename)); //Suggested file name in the "Save as" dialog which will be displayed to the end user }
public void BuildMultilistingUSFBAExcel(IList <FBAItemInfo> fbaItems) { var resultItems = new List <ExcelProductUSViewModel>(); var marketplaceManager = new MarketplaceKeeper(_dbFactory, false); marketplaceManager.Init(); IMarketApi api = new MarketFactory(marketplaceManager.GetAll(), _time, _log, _dbFactory, null) .GetApi(AccessManager.Company.Id, MarketType.Amazon, MarketplaceKeeper.AmazonComMarketplaceId); var htmlScraper = new HtmlScraperService(_log, _time, _dbFactory); var filename = String.Empty; using (var db = _dbFactory.GetRWDb()) { var parentASINList = fbaItems.Select(i => i.ParentASIN).Distinct().ToList(); foreach (var parentASIN in parentASINList) { var childFBAItems = fbaItems.Where(f => f.ParentASIN == parentASIN).ToList(); var newItems = ExcelProductUSViewModel.GetItemsFor(_log, _time, _categoryService, htmlScraper, api, db, _company, parentASIN, ExportToExcelMode.FBA, childFBAItems, MarketType.Amazon, MarketplaceKeeper.AmazonComMarketplaceId, UseStyleImageModes.Auto, out filename); resultItems.AddRange(newItems); } } var templateFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files/Templates/Flat.File.Clothing-full.OneSheet.US.xls"); var stream = ExcelHelper.ExportIntoFile(templateFile, "Template", resultItems); var outputFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, String.Format("USFBAListings_{0}.xls", _time.GetAppNowTime().ToString("MM_dd_yyyy_hh_mm_ss"))); stream.Seek(0, SeekOrigin.Begin); using (FileStream file = new FileStream(outputFile, FileMode.Create, System.IO.FileAccess.Write)) { byte[] bytes = new byte[stream.Length]; stream.Read(bytes, 0, (int)stream.Length); file.Write(bytes, 0, bytes.Length); stream.Close(); } }
public static MemoryStream ExportToExcel(long pickListId, IUnitOfWork db, IDbFactory dbFactory, CompanyDTO company, IMarketCategoryService categoryService, IHtmlScraperService htmlScraper, ILogService log, ITime time, out string filename) { var resultItems = new List <ExcelProductUSViewModel>(); var pickListEntries = db.FBAPickListEntries.GetAllAsDto() .Where(p => p.FBAPickListId == pickListId) .ToList(); IList <FBAItemInfo> fbaItems = pickListEntries.Select(e => new FBAItemInfo() { ParentASIN = e.ListingParentASIN, ASIN = e.ListingASIN, SKU = e.ListingSKU, Quantity = e.Quantity, }).ToList(); var marketplaceManager = new MarketplaceKeeper(dbFactory, false); marketplaceManager.Init(); IMarketApi api = new MarketFactory(marketplaceManager.GetAll(), time, log, dbFactory, null) .GetApi(AccessManager.Company.Id, MarketType.Amazon, MarketplaceKeeper.AmazonComMarketplaceId); var parentASINList = fbaItems.Select(i => i.ParentASIN).Distinct().ToList(); foreach (var parentASIN in parentASINList) { var childFBAItems = fbaItems.Where(f => f.ParentASIN == parentASIN).ToList(); var newItems = ExcelProductUSViewModel.GetItemsFor(log, time, categoryService, htmlScraper, api, db, company, parentASIN, ExportToExcelMode.FBA, childFBAItems, MarketType.Amazon, MarketplaceKeeper.AmazonComMarketplaceId, UseStyleImageModes.Auto, out filename); var parentItem = newItems.FirstOrDefault(i => i.Parentage == ExcelHelper.ParentageParent); var wasAddedAny = false; var notExistNewItems = new List <ExcelProductUSViewModel>(); foreach (var item in newItems.Where(i => i.Parentage != ExcelHelper.ParentageParent)) { var existListing = db.Listings.GetAll().FirstOrDefault(l => l.SKU == item.SKU && !l.IsRemoved); if (existListing == null) { notExistNewItems.Add(item); wasAddedAny = true; } } if (wasAddedAny) { if (parentItem != null) { resultItems.Add(parentItem); } resultItems.AddRange(notExistNewItems); } } var output = ExcelHelper.ExportIntoFile(HttpContext.Current.Server.MapPath(ExcelProductUSViewModel.USTemplatePath), "Template", resultItems); filename = String.Format("FBAListings_{0}.xls", time.GetAppNowTime().ToString("MM_dd_yyyy_hh_mm_ss")); return(output); }
public void BuildMultilistingUSPrimeExcelByChildASIN(IList <string> usChildASINList) { var resultItems = new List <ExcelProductUSViewModel>(); var marketplaceManager = new MarketplaceKeeper(_dbFactory, false); marketplaceManager.Init(); IMarketApi api = new MarketFactory(marketplaceManager.GetAll(), _time, _log, _dbFactory, null) .GetApi(AccessManager.Company.Id, MarketType.Amazon, MarketplaceKeeper.AmazonComMarketplaceId); var htmlScraper = new HtmlScraperService(_log, _time, _dbFactory); var filename = String.Empty; using (var db = _dbFactory.GetRWDb()) { var parentASINs = db.Items.GetAll().Where(i => usChildASINList.Contains(i.ASIN) && i.Market == (int)MarketType.Amazon && i.MarketplaceId == MarketplaceKeeper.AmazonComMarketplaceId) .Select(i => i.ParentASIN) .ToList(); var usASINList = parentASINs.Distinct().ToList(); foreach (var asin in usASINList) { var newItems = ExcelProductUSViewModel.GetItemsFor(_log, _time, _categoryService, htmlScraper, api, db, _company, asin, ExportToExcelMode.FBP, null, MarketType.Amazon, MarketplaceKeeper.AmazonComMarketplaceId, UseStyleImageModes.Auto, out filename); var resultList = new List <ExcelProductUSViewModel>(); foreach (var newItem in newItems) { if (newItem.StyleItemId.HasValue) { var styleItem = db.StyleItemCaches.GetForStyleItemId(newItem.StyleItemId.Value).FirstOrDefault(); if (styleItem != null && styleItem.RemainingQuantity > 0) { resultList.Add(newItem); } } else { //Parent record w/o StyleItemId resultList.Add(newItem); } } resultItems.AddRange(resultList); } } var templateFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Files/Templates/Flat.File.Clothing-full.OneSheet.US.xls"); var stream = ExcelHelper.ExportIntoFile(templateFile, "Template", resultItems); var outputFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, String.Format("USPrimeListings_{0}.xls", _time.GetAppNowTime().ToString("MM_dd_yyyy_hh_mm_ss"))); stream.Seek(0, SeekOrigin.Begin); using (FileStream file = new FileStream(outputFile, FileMode.Create, System.IO.FileAccess.Write)) { byte[] bytes = new byte[stream.Length]; stream.Read(bytes, 0, (int)stream.Length); file.Write(bytes, 0, bytes.Length); stream.Close(); } }
public string Export(IUnitOfWork db, ITime time, ILogService log, IBarcodeService barcodeService, IMarketCategoryService categoryService, IMarketplaceService marketplaceService, DateTime when, long?by) { log.Info("Export, parentId=" + Id + ", market=" + Market + ", marketplaceId=" + MarketplaceId); string filename = null; var parent = db.ParentItems.GetAsDTO((int)Id); var parentImage = db.ParentItemImages.GetAllAsDto().FirstOrDefault(pi => pi.ItemId == parent.Id); parent.LargeImage = parentImage; var parentChildren = db.Items.GetAllActualExAsDto().Where(i => i.ParentASIN == parent.ASIN && //NOTE: use original ASIN i.Market == (int)parent.Market && i.MarketplaceId == parent.MarketplaceId).ToList(); //if (exportMode != ExportToExcelMode.FBA) //TASK: Generate excel only for listing that haven't FBA parentChildren = parentChildren.Where(i => !i.IsFBA).ToList(); parentChildren = parentChildren .OrderBy(ch => ch.StyleString) .ThenBy(ch => SizeHelper.GetSizeIndex(ch.Size)) .ToList(); var children = VariationList .Where(v => v.IsSelected && !String.IsNullOrEmpty(v.StyleString)) .Select(v => new ItemExDTO() { StyleString = v.StyleString, StyleId = v.StyleId, StyleItemId = v.StyleItemId, Barcode = v.Barcode, SKU = v.SKU, Size = v.Size, Color = v.Color, CurrentPrice = v.Price, }).ToList(); parent.ASIN = ASIN; parent.AmazonName = Name; var styleStringList = children.Select(ch => ch.StyleString).Distinct().ToList(); var styles = db.Styles.GetAllActive().Where(s => styleStringList.Contains(s.StyleID)).ToList(); for (int i = 0; i < children.Count; i++) { var style = styles.FirstOrDefault(s => s.StyleID == children[i].StyleString); if (style != null) { children[i].StyleId = style.Id; } var existParentChild = parentChildren.FirstOrDefault(p => String.Compare(p.SKU, children[i].SKU, StringComparison.OrdinalIgnoreCase) == 0); if (existParentChild != null) { children[i].Id = existParentChild.Id; children[i].ASIN = existParentChild.ASIN; children[i].Name = existParentChild.Name; children[i].ImageUrl = existParentChild.ImageUrl; children[i].IsExistOnAmazon = existParentChild.IsExistOnAmazon; children[i].ListPrice = existParentChild.ListPrice; children[i].BrandName = existParentChild.BrandName; children[i].SpecialSize = existParentChild.SpecialSize; children[i].Features = existParentChild.Features; children[i].Department = existParentChild.Department; children[i].RealQuantity = existParentChild.RealQuantity; children[i].Weight = existParentChild.Weight; //NOTE: only for FBA children[i].IsPrime = existParentChild.IsPrime; } } var resultRecords = ExcelProductUSViewModel.GetItemsFor(db, categoryService, ExportToExcelMode.Normal, null, parent, children, PictureSourceType == PictureSourceTypes.FromStyle ? UseStyleImageModes.StyleImage : UseStyleImageModes.ListingImage, out filename); //NOTE: mark already exist listings as PartialUpdate foreach (var resultRecord in resultRecords) { if (resultRecord.Id > 0 && resultRecord.Parentage == ExcelHelper.ParentageChild && resultRecord.IsExistOnAmazon) { resultRecord.Update = "PartialUpdate"; resultRecord.Quantity = null; resultRecord.StandardPrice = null; //resultRecord.SuggestedPrice = null; //resultRecord.MainImageURL = null; //resultRecord.OtherImageUrl1 = null; //resultRecord.OtherImageUrl2 = null; //resultRecord.OtherImageUrl3 = null; } } var firstResult = resultRecords.FirstOrDefault(r => r.Parentage == ExcelHelper.ParentageChild); if (firstResult != null) { foreach (var resultRecord in resultRecords) { if (CopyBulletPoints && resultRecord.Id == 0 && resultRecord.Parentage == ExcelHelper.ParentageChild) { resultRecord.KeyProductFeatures1 = firstResult.KeyProductFeatures1; resultRecord.KeyProductFeatures2 = firstResult.KeyProductFeatures2; resultRecord.KeyProductFeatures3 = firstResult.KeyProductFeatures3; resultRecord.KeyProductFeatures4 = firstResult.KeyProductFeatures4; resultRecord.KeyProductFeatures5 = firstResult.KeyProductFeatures5; resultRecord.SearchTerms1 = firstResult.SearchTerms1; } } } var styleStringToGenerate = children.Where(ch => ch.Id == 0).Select(ch => ch.StyleString).Distinct().ToList(); foreach (var styleString in styleStringToGenerate) { var styleDto = db.Styles.GetActiveByStyleIdAsDto(styleString); var styleModel = new StyleViewModel(db, marketplaceService, styleDto); //Update Barcode and AutoGenerateBarcode flag, other info already exists in resultRecords foreach (var si in styleModel.StyleItems.Items) { var variationItem = VariationList.FirstOrDefault(v => v.StyleItemId == si.Id); if (variationItem != null) { if (!String.IsNullOrEmpty(variationItem.Barcode)) { si.Barcodes = new[] { new BarcodeDTO() { Barcode = variationItem.Barcode } } } ; si.AutoGeneratedBarcode = variationItem.AutoGeneratedBarcode; } } string tempFilename = null; var styleResultRecords = ExcelProductUSViewModel.GenerateToExcelUS(db, barcodeService, categoryService, styleModel, when, out tempFilename); foreach (var styleResultRecord in styleResultRecords) { if (!styleResultRecord.StyleItemId.HasValue) { continue; } var resultRecord = resultRecords.FirstOrDefault(s => s.StyleItemId == styleResultRecord.StyleItemId); if (resultRecord != null) { resultRecord.ASIN = styleResultRecord.ASIN; resultRecord.ProductId = styleResultRecord.ProductId; resultRecord.Title = styleResultRecord.Title; resultRecord.Description = styleResultRecord.Description; resultRecord.BrandName = styleResultRecord.BrandName; resultRecord.Type = styleResultRecord.Type; resultRecord.MainImageURL = styleResultRecord.MainImageURL; resultRecord.OtherImageUrl1 = styleResultRecord.OtherImageUrl1; resultRecord.OtherImageUrl2 = styleResultRecord.OtherImageUrl2; resultRecord.OtherImageUrl3 = styleResultRecord.OtherImageUrl3; resultRecord.Department = styleResultRecord.Department; resultRecord.SuggestedPrice = styleResultRecord.SuggestedPrice; resultRecord.Quantity = styleResultRecord.Quantity; if (!CopyBulletPoints) { resultRecord.KeyProductFeatures1 = styleResultRecord.KeyProductFeatures1; resultRecord.KeyProductFeatures2 = styleResultRecord.KeyProductFeatures2; resultRecord.KeyProductFeatures3 = styleResultRecord.KeyProductFeatures3; resultRecord.KeyProductFeatures4 = styleResultRecord.KeyProductFeatures4; resultRecord.KeyProductFeatures5 = styleResultRecord.KeyProductFeatures5; resultRecord.SearchTerms1 = styleResultRecord.SearchTerms1; } } } } var stream = ExcelHelper.ExportIntoFile(HttpContext.Current.Server.MapPath(ExcelProductUSViewModel.USTemplatePath), "Template", resultRecords); var filePath = UrlHelper.GetProductTemplateFilePath(filename); using (var file = File.Open(filePath, FileMode.Create)) { stream.WriteTo(file); } return(UrlHelper.GetProductTemplateUrl(filename)); } }