public void TableExecutor()
{
long?value = 0;
int bulkSize = 1;
Dictionary <string, string> dic = new Dictionary <string, string>();
foreach (var product in allProducts)
{
try
{
if (product != null && !dic.ContainsKey(product.ItemId))
{
Wholesaler_Fragrancex fran = new Wholesaler_Fragrancex();
fran.id = bulkSize + 1;
fran.Sku = Convert.ToInt32(product.ItemId);
fran.BrandName = product.BrandName;
fran.Description = product.Description;
fran.Gender = product.Gender;
fran.Instock = product.Instock;
fran.LargeImageUrl = product.LargeImageUrl;
fran.MetricSize = product.MetricSize;
fran.ParentCode = product.ParentCode;
fran.ProductName = product.ProductName;
fran.RetailPriceUSD = product.RetailPriceUSD;
fran.Size = product.Size;
fran.SmallImageURL = product.SmallImageUrl;
fran.Type = product.Type;
fran.WholePriceAUD = product.WholesalePriceAUD;
fran.WholePriceCAD = product.WholesalePriceCAD;
fran.WholePriceEUR = product.WholesalePriceEUR;
fran.WholePriceGBP = product.WholesalePriceGBP;
fran.WholePriceUSD = product.WholesalePriceUSD;
if (upc.TryGetValue(Convert.ToInt32(product.ItemId), out value))
{
fran.Upc = value;
}
dic.Add(fran.Sku.ToString(), fran.Sku.ToString());
fragrancex.Add(fran);
}
}
catch (Exception e)
{
throw;
}
}
//upload(uploadFragrancex, bulkSize, "dbo.Fragrancex");
}
public virtual void TableExecutor()
{
List <UPC> list = new List <UPC>();
FileInfo file = new FileInfo(path);
int exception = 0;
try
{
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
long? value = 0;
int itemID = 0, description = 0, price = 0, brandName = 0,
image = 0, type = 0;
for (int row = 1; row <= rowCount; row++)
{
// Map titles
if (row == 1)
{
for (int column = 1; column <= worksheet.Dimension.Columns; column++)
{
if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("sku"))
{
itemID = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("html"))
{
description = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("variant price"))
{
price = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("vendor"))
{
brandName = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("image src"))
{
image = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("type"))
{
type = column;
}
}
}
else
{
exception++;
if (!franDic.ContainsKey(Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString())))
{
Wholesaler_Fragrancex fran = new Wholesaler_Fragrancex();
fran.id = exception;
fran.Sku = Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString());
fran.BrandName = worksheet.Cells[row, brandName].Value?.ToString();
fran.Description = worksheet.Cells[row, description].Value?.ToString();
fran.Gender = null;
fran.Instock = true;
fran.LargeImageUrl = worksheet.Cells[row, image].Value?.ToString();
fran.MetricSize = null;
fran.ParentCode = null;
fran.ProductName = null;
fran.RetailPriceUSD = 0.0;
fran.Size = null;
fran.SmallImageURL = null;
fran.Type = worksheet.Cells[row, type].Value?.ToString();
fran.WholePriceAUD = 0.0;
fran.WholePriceCAD = 0.0;
fran.WholePriceEUR = 0.0;
fran.WholePriceGBP = 0.0;
fran.WholePriceUSD = Convert.ToDouble(worksheet.Cells[row, price].Value?.ToString());
if (upc.TryGetValue(Convert.ToInt32(Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString())), out value))
{
fran.Upc = value;
}
fragancexList.Add(fran);
franDic.Add(Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString()), Convert.ToInt32(worksheet.Cells[row, itemID].Value?.ToString()));
}
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
public void saveExcel()
{
FileInfo file = new FileInfo(path);
try
{
using (ExcelPackage package = new ExcelPackage(file))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
// delete everything from the spreadsheet
worksheet.DeleteRow(2, worksheet.Dimension.Rows);
string itemID;
string title = "";
int row = 2;
int execption = 0;
setTitleDic();
try
{
foreach (var item in shopifyProfile
.OrderBy(x => x.Value.handle)
.OrderBy(x => x.Value.option1Value))
{
if (shopifyUser.ContainsKey(item.Value.sku + "_" + profile.ProfileUser))
{
execption++;
itemID = item.Value.sku;
Wholesaler_Fragrancex fra = new Wholesaler_Fragrancex();
fragrancex.TryGetValue(Convert.ToInt32(itemID), out fra);
string description = string.Empty;
double price = 0.0;
if (fra != null)
{
description = fra.Description;
price = fra.WholePriceUSD;
}
// Set Handle
worksheet.Cells[row, 1].Value = item.Value.handle;
// Logic for the title
title = BuildTitle(dicTitle, item.Value.handle, item.Value.collection);
worksheet.Cells[row, 2].Value = title;
//Logic for the HTML Body
worksheet.Cells[row, 3].Value = BuildHTML(title, row, profile.html, item.Value.image
, description);
// Set Vendor
worksheet.Cells[row, 4].Value = item.Value.vendor;
// Set Type
worksheet.Cells[row, 5].Value = item.Value.type;
// Set Publish
worksheet.Cells[row, 6].Value = "TRUE";
// Option1 Name
worksheet.Cells[row, 7].Value = "Size";
// Option1 Value
worksheet.Cells[row, 8].Value = item.Value.option1Value;
// Set SKU
worksheet.Cells[row, 13].Value = item.Value.sku;
// Set Variant
worksheet.Cells[row, 14].Value = 400;
// Set Variant Inventory Tracker
worksheet.Cells[row, 15].Value = "shopify";
// Set Variant Inventory Policy
worksheet.Cells[row, 17].Value = "deny";
// Set Variant Fulfillment Service
worksheet.Cells[row, 18].Value = "manual";
// Set Variant Compare At Price
//if (item.Value.comparePrice != 0)
//{
// worksheet.Cells[row, 20].Value = item.Value.comparePrice;
//}
// Set Variant Requires Shipping
worksheet.Cells[row, 21].Value = "TRUE";
// Set Variant Taxable
worksheet.Cells[row, 22].Value = "FALSE";
// UPC creator
UPC upcs = new UPC();
upc.TryGetValue(Convert.ToInt32(itemID), out upcs);
if (upcs != null)
{
worksheet.Cells[row, 23].Value = upcs.Upc;
}
// Set Image
worksheet.Cells[row, 24].Value = fixPictureHTML(item.Value.image);
// Set Tags
worksheet.Cells[row, 26].Value = item.Value.tags;
// Set Collection
worksheet.Cells[row, 27].Value = item.Value.collection;
// Prices
if (price != 0.0)
{
// Set Price
worksheet.Cells[row, 19].Value = getSellingPrice(price);
// Set Variant Inventory Qty
worksheet.Cells[row, 16].Value = profile.items;
}
else
{
// Set Price
worksheet.Cells[row, 19].Value = 100.0;
// Set Variant Inventory Qty
worksheet.Cells[row, 16].Value = 0;
}
worksheet.Cells[row, 28].Value = price;
row++;
}
}
}
catch (Exception e)
{
throw e;
}
package.Save();
}
}
catch (Exception ex)
{
throw ex;
}
}
public void ExcelGenerator()
{
FileInfo file = new FileInfo(path);
long? skuID;
int execption = 0;
try
{
using (ExcelPackage package = new ExcelPackage(file))
{
StringBuilder sb = new StringBuilder();
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int rowCount = worksheet.Dimension.Rows;
int ColCount = worksheet.Dimension.Columns;
int sku = 0;
int price = 0;
int quantity = 0;
int asinCol = 0;
for (int row = 1; row <= rowCount; row++)
{
execption++;
if (row == 1)
{
for (int column = 1; column <= ColCount; column++)
{
if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("sku"))
{
sku = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("price"))
{
price = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("quantity") ||
worksheet.Cells[row, column].Value.ToString().ToLower().Contains("qty"))
{
quantity = column;
}
else if (worksheet.Cells[row, column].Value.ToString().ToLower().Contains("asin"))
{
asinCol = column;
}
}
worksheet.Cells[row, 1].Value = "sku";
worksheet.Cells[row, 2].Value = "price";
worksheet.Cells[row, 3].Value = "minimum-seller-allowed-price";
worksheet.Cells[row, 4].Value = "maximum-seller-allowed-price";
worksheet.Cells[row, 5].Value = "quantity";
worksheet.Cells[row, 6].Value = "handling-time";
worksheet.Cells[row, 7].Value = "fulfillment-channel";
worksheet.Cells[row, 8].Value = "Suggested Price";
worksheet.Cells[row, 9].Value = "Weight Price";
}
else
{
if (!string.IsNullOrEmpty(worksheet.Cells[row, 1].Value?.ToString()))
{
// if the first row is a perfume/Cologne
string rowSku = worksheet.Cells[row, 1].Value.ToString();
int digitSku = DigitGetter(rowSku);
double rowPrice = Convert.ToDouble(worksheet.Cells[row, price].Value);
string asin = Convert.ToString(worksheet.Cells[row, asinCol].Value);
if (isBlackListed(asin))
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Pink);
worksheet.Cells[row, 5].Value = 0;
worksheet.Cells[row, 8].Value = "ASIN Black Listed";
}
else if (isFragrancex(digitSku))
{
// In-stock
if (fragrancexList.ContainsKey(digitSku))
{
skuID = DigitGetter(rowSku);
Wholesaler_Fragrancex f = new Wholesaler_Fragrancex();
fragrancexList.TryGetValue(Convert.ToInt32(skuID), out f);
fragrancex = f;
double sellingPrice = getSellingPrice(fragrancex.WholePriceUSD);
// Price lower
if (isPriceLower(rowPrice, sellingPrice) && sellingPrice != 0)
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Red);
worksheet.Cells[row, 8].Value = sellingPrice;
}
// The price is too high
else if (isPriceTooHigh(rowPrice, sellingPrice) && sellingPrice != 0)
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = 3;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue);
worksheet.Cells[row, 8].Value = sellingPrice;
}
else if (sellingPrice != 0)
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = 3;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Green);
worksheet.Cells[row, 8].Value = sellingPrice;
}
}
// Out of stock
else
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = 0;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
}
}
else if (isAzImporter(rowSku))
{
double sellingPrice = getSellingPrice();
// In-stock
if (azImporter.Quantity > 0)
{
// Weight is not register
if (!isWeightRegister(AzImporterPriceWeight))
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Orange);
worksheet.Cells[row, 8].Value = "Weight Not Register";
}
else
{
// Price too low
if (isPriceLower(rowPrice, sellingPrice) && sellingPrice != 0)
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Red);
worksheet.Cells[row, 8].Value = sellingPrice;
worksheet.Cells[row, 9].Value = azImporter.Weight;
}
// Price is too high
else if (isPriceTooHigh(rowPrice, sellingPrice) && sellingPrice != 0)
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = 3;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue);
worksheet.Cells[row, 8].Value = sellingPrice;
worksheet.Cells[row, 9].Value = azImporter.Weight;
}
else
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = 3;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Green);
worksheet.Cells[row, 8].Value = sellingPrice;
worksheet.Cells[row, 9].Value = azImporter.Weight;
}
}
}
// Out of stock
else
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = 0;
worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
worksheet.Cells[row, 8].Value = sellingPrice;
worksheet.Cells[row, 9].Value = azImporter.Weight;
}
azImporterSku = "";
AzImporterPriceWeight = 0.0;
AzImporterWeight = 0.0;
}
//else if(isPerfumeWorldWide(rowSku))
//{
// // In-stock
// if (perfumeWorldWideList.ContainsKey(rowSku))
// {
// PerfumeWorldWide p = new PerfumeWorldWide();
// perfumeWorldWideList.TryGetValue(rowSku, out p);
// perfumeWorldWide = p;
// double sellingPrice = getSellingPrice(perfumeWorldWide.Cost);
// // Price lower
// if (isPriceLower(rowPrice, sellingPrice) && sellingPrice != 0)
// {
// worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
// worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value;
// worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
// worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Red);
// worksheet.Cells[row, 8].Value = sellingPrice;
// }
// // The price is too high
// else if (isPriceTooHigh(rowPrice, sellingPrice) && sellingPrice != 0)
// {
// worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
// worksheet.Cells[row, 5].Value = 3;
// worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
// worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue);
// worksheet.Cells[row, 8].Value = sellingPrice;
// }
// else if (sellingPrice != 0)
// {
// worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
// worksheet.Cells[row, 5].Value = 3;
// worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
// worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Green);
// worksheet.Cells[row, 8].Value = sellingPrice;
// }
// }
// // Out-stock
// else
// {
// worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
// worksheet.Cells[row, 5].Value = 0;
// worksheet.Cells[row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
// worksheet.Cells[row, 5].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
// }
//}
else
{
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = worksheet.Cells[row, quantity].Value;
worksheet.Cells[row, 2].Value = worksheet.Cells[row, price].Value;
worksheet.Cells[row, 5].Value = 0;
}
worksheet.Cells[row, 3].Value = "";
worksheet.Cells[row, 4].Value = "";
}
}
}
int start = 2;
worksheet.Cells[execption + start, 1].Value = "Legend";
start++;
worksheet.Cells[execption + start, 1].Value = "Out of stock";
worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
start++;
worksheet.Cells[execption + start, 1].Value = "Weight not Register";
worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Orange);
start++;
worksheet.Cells[execption + start, 1].Value = "Price is too High";
worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.MediumBlue);
start++;
worksheet.Cells[execption + start, 1].Value = "Price is too Low";
worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Red);
start++;;
worksheet.Cells[execption + start, 1].Value = "Price is Correct";
worksheet.Cells[execption + start, 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[execption + start, 2].Style.Fill.BackgroundColor.SetColor(Color.Green);
package.Save();
}
}
catch (Exception ex)
{
throw (ex);
}
}
