private bool isInDB(string sku)
{
if (PerfumeWorldWide.ContainsKey(sku))
{
return(true);
}
else
{
return(false);
}
}
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);
Fragrancex f = new 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);
}
}
private void DatabaseFieldSet()
{
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;
int code = 0, brand = 0, designer = 0, size = 0, type = 0, set = 0, msrp = 0
, gender = 0, description = 0, image = 0, cost = 0, weight = 0, upc = 0;
// Set all of the isInstock to false in the dictionary
PerfumeWorldWide.ToDictionary(x => x.Key, x => x.Value.isInstock = false);
for (int row = 1; row <= rowCount; row++)
{
if (row == 1)
{
for (int i = 1; i <= worksheet.Dimension.Columns; i++)
{
if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("code"))
{
code = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, 2].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("brand"))
{
brand = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("designer"))
{
designer = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("size"))
{
size = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("type"))
{
type = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("gender"))
{
gender = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("set"))
{
set = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("description"))
{
description = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("image"))
{
image = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("cost"))
{
cost = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("weight"))
{
weight = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("msrp"))
{
msrp = i;
}
else if (!string.IsNullOrEmpty(worksheet.Cells[row, i].Value.ToString().ToLower()) &&
worksheet.Cells[row, i].Value.ToString().ToLower().Equals("upc"))
{
upc = i;
}
}
}
else
{
exception++;
string sku = worksheet.Cells[row, code].Value?.ToString();
if (!string.IsNullOrEmpty(sku) && isInDB(sku))
{
PerfumeWorldWide.Where(x => x.Key == sku).FirstOrDefault().Value.isInstock = true;
}
else
{
PerfumeWorldWide p = new PerfumeWorldWide();
p.sku = sku;
p.Brand = worksheet.Cells[row, brand].Value?.ToString();
p.Designer = worksheet.Cells[row, designer].Value?.ToString();
p.Size = worksheet.Cells[row, size].Value?.ToString();
p.Type = worksheet.Cells[row, type].Value?.ToString();
p.Gender = worksheet.Cells[row, gender].Value?.ToString();
p.Set = worksheet.Cells[row, set].Value?.ToString();
p.Designer = worksheet.Cells[row, designer].Value?.ToString();
p.Image = worksheet.Cells[row, image].Value?.ToString();
p.Cost = Convert.ToDouble(worksheet.Cells[row, cost].Value?.ToString());
p.Weight = Convert.ToDouble(worksheet.Cells[row, weight].Value?.ToString());
p.MSRP = Convert.ToDouble(worksheet.Cells[row, msrp].Value?.ToString());
p.upc = Convert.ToInt64(worksheet.Cells[row, upc].Value?.ToString());
p.isInstock = true;
PerfumeWorldWide.Add(sku, p);
}
}
}
}
}
catch (Exception ex)
{
throw (ex);
}
}
