public Fragrancex getFragrancex2(string itemID)
{
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.AddEnvironmentVariables();
IConfiguration Configuration;
builder.AddEnvironmentVariables();
Configuration = builder.Build();
string connectionstring = Configuration.GetConnectionString("BloggingDatabase");
using (SqlConnection sourceConnection = new SqlConnection(connectionstring))
{
sourceConnection.Open();
SqlCommand commandSourceData = new SqlCommand(
"SELECT * FROM Fragrancex WHERE "
+ "itemID = " + "'" + itemID + "'", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();
Fragrancex fragrancex = new Fragrancex();
while (reader.Read())
{
fragrancex.ItemID = Convert.ToInt32(reader[0]);
fragrancex.BrandName = reader[1].ToString();
fragrancex.Description = reader[2].ToString();
fragrancex.Gender = reader[3].ToString();
fragrancex.WholePriceUSD = Convert.ToInt32(reader[17]);
}
sourceConnection.Close();
return(fragrancex);
}
}
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;
Fragrancex fra = new 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);
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);
}
}
