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