public static void SaveCategoriesMappingFromProductsListToDB()
{
var productCategoriesWorkbook = ExcelProdvider.GetWorkbook("_data/nopVareMapping.xlsx");
var excelProducts = ExcelProdvider.GetExcelData(productCategoriesWorkbook, 1, 30, true);
var headers = ExcelProdvider.GetExcelHeaders(excelProducts);
excelProducts.RemoveAt(0);
Console.WriteLine("Excel data loaded!");
foreach (var product in context.Products)
{
Console.WriteLine(product.Id);
var excelProduct = excelProducts.FirstOrDefault(ep => ep[headers["ProductId"]] == product.OmnishopProductId);
if (excelProduct != null)
{
var category = context.Categories.FirstOrDefault(c => c.OmnishopCategoryId == excelProduct[headers["CategoryId"]]);
if (category != null && !context.ProductCategories.Any(pc => pc.ProductId == product.Id && pc.CategoryId == category.Id))
{
var newProductCategory = new ProductCategory();
newProductCategory.ProductId = product.Id;
newProductCategory.CategoryId = category.Id;
context.ProductCategories.Add(newProductCategory);
context.SaveChanges();
}
}
}
}
public static void SaveCategoriesMappingFromProductCategoriesListToDB()
{
var productCategoriesWorkbook = ExcelProdvider.GetWorkbook("_data/nopVareMapping.xlsx");
var productCategories = ExcelProdvider.GetExcelData(productCategoriesWorkbook, 2, 2, false);
var categoryNodes = ExcelProdvider.GetExcelData(productCategoriesWorkbook, 3, 10, false);
var pcHeaders = ExcelProdvider.GetExcelHeaders(productCategories);
var cnHeaders = ExcelProdvider.GetExcelHeaders(categoryNodes);
var tlCategoryNodes = categoryNodes.Where(n => n[cnHeaders["treeLevel"]] == "3").ToList();
var folCategoryNodes = categoryNodes.Where(n => n[cnHeaders["treeLevel"]] == "4").ToList();
Console.WriteLine("Excel data loaded!");
productCategories.RemoveAt(0);
categoryNodes.RemoveAt(0);
foreach (var product in context.Products)
{
Console.WriteLine(product.Id);
foreach (var productCategory in productCategories.Where(pc => pc[pcHeaders["ProductId"]] == product.OmnishopProductId))
{
var categoryNodeId = folCategoryNodes.FirstOrDefault(foln => foln[cnHeaders["nodeId"]] == productCategory[pcHeaders["CategoryNodeId"]])?[cnHeaders["parentNodeId"]] ??
(tlCategoryNodes.FirstOrDefault(tln => tln[cnHeaders["nodeId"]] == productCategory[pcHeaders["CategoryNodeId"]])?[cnHeaders["parentNodeId"]] ?? null);
if (categoryNodeId != null)
{
var categoryNode = context.Categories.FirstOrDefault(c => c.NodeId == categoryNodeId);
if (categoryNode != null && !context.ProductCategories.Any(pc => pc.ProductId == product.Id && pc.CategoryId == categoryNode.Id))
{
var newProductCategory = new ProductCategory();
newProductCategory.ProductId = product.Id;
newProductCategory.CategoryId = categoryNode.Id;
context.ProductCategories.Add(newProductCategory);
context.SaveChanges();
}
}
}
}
Console.WriteLine("Product categories checked!");
}
public static void SaveCategoriesToDB()
{
var productCategoriesWorkbook = ExcelProdvider.GetWorkbook("_data/nopVareMapping.xlsx");
var categoryNodes = ExcelProdvider.GetExcelData(productCategoriesWorkbook, 3, 10, false);
var cnHeaders = ExcelProdvider.GetExcelHeaders(categoryNodes);
var flCategoryNodes = categoryNodes.Where(n => n[cnHeaders["treeLevel"]] == "1").ToList();
var slCategoryNodes = categoryNodes.Where(n => n[cnHeaders["treeLevel"]] == "2").ToList();
var tlCategoryNodes = categoryNodes.Where(n => n[cnHeaders["treeLevel"]] == "3").ToList();
var fullCategoriesList = new List <List <List <string> > >()
{
flCategoryNodes, slCategoryNodes, tlCategoryNodes
};
Console.WriteLine("Excel data loaded!");
foreach (var categoryNodeList in fullCategoriesList)
{
foreach (var categoryNode in categoryNodeList)
{
if (!context.Categories.Any(cn => cn.OmnishopCategoryId == categoryNode[cnHeaders["Omnishop ProductCategoryId"]]))
{
var category = new Category();
category.NodeId = categoryNode[cnHeaders["nodeId"]];
category.OmnishopCategoryId = categoryNode[cnHeaders["Omnishop ProductCategoryId"]];
category.Name = categoryNode[cnHeaders["Omnishop Name"]];
category.MenuTitle = categoryNode[cnHeaders["menuTitle"]];
category.SortOrder = int.TryParse(categoryNode[cnHeaders["sortOrder"]], out int order) ? order : 0;
category.Level = int.TryParse(categoryNode[cnHeaders["treeLevel"]], out int level) ? level : 0;
category.ParentCategoryId = context.Categories.FirstOrDefault(c => c.NodeId == categoryNode[cnHeaders["parentNodeId"]])?.Id ?? null;
context.Categories.Add(category);
context.SaveChanges();
}
}
}
}
public static void SaveProductsMappingToDB()
{
//var productCategoriesWorkbook = ExcelProdvider.GetWorkbook("_data/nopVareMapping.xlsx");
//var products = ExcelProdvider.GetExcelData(productCategoriesWorkbook, 1, 30, true);
//File.WriteAllText($"_data/productsNopVare.json", JsonConvert.SerializeObject(products));
var products = JsonConvert.DeserializeObject <List <List <string> > >(File.ReadAllText($"_data/productsNopVare.json"));
var headers = ExcelProdvider.GetExcelHeaders(products);
var descriptions = GetLongDescriptions();
products.RemoveAt(0);
int j = 0;
foreach (var product in products)
{
Console.WriteLine(++j);
if (!string.IsNullOrEmpty(product[headers["SupplierId"]]) &&
!string.IsNullOrEmpty(product[headers["ProductId"]]) &&
!string.IsNullOrEmpty(product[headers["SupplierName"]]) &&
!string.IsNullOrEmpty(product[headers["SupplierProductId"]]))
{
var supplier = context.Suppliers.FirstOrDefault(s => s.SupplierId == product[headers["SupplierId"]]);
var isNew = supplier == null;
if (isNew)
{
supplier = new Supplier();
supplier.SupplierId = product[headers["SupplierId"]];
supplier.SupplierName = product[headers["SupplierName"]];
context.Suppliers.Add(supplier);
context.SaveChanges();
}
var newProduct = context.Products.FirstOrDefault(p => p.OmnishopProductId == product[headers["ProductId"]]);
isNew = newProduct == null;
if (isNew)
{
newProduct = new Product();
newProduct.OmnishopProductId = product[headers["ProductId"]];
context.Products.Add(newProduct);
var productSupplier = new ProductSupplier();
productSupplier.ProductId = newProduct.Id;
productSupplier.SupplierId = supplier.Id;
productSupplier.ProductSupplierId = product[headers["SupplierProductId"]];
context.ProductSuppliers.Add(productSupplier);
var uProduct = new UProduct();
uProduct.ProductId = newProduct.Id;
uProduct.UProductId = product[headers["ProductId2"]];
uProduct.PimSKU = product[headers["PimSKU"]];
uProduct.ContainerModel = product[headers["ContainerModel"]];
uProduct.VariantModel = product[headers["VariantModel"]];
uProduct.FullModel = product[headers["FullModel"]];
uProduct.DisplayName = product[headers["DisplayName"]];
uProduct.ShortDescription = product[headers["ShortDescription"]];
uProduct.LongDescription = descriptions.FirstOrDefault(d => d[0].Trim() == uProduct.UProductId.Trim())?[1] ?? "";
uProduct.SuggestedRetailPriceIncVat = double.TryParse(product[headers["SuggestedRetailPriceIncVat"]], out double suggestedRetailPriceIncVat) ? suggestedRetailPriceIncVat : 0;
uProduct.Price = double.TryParse(product[headers["Price"]], out double price) ? price : 0;
uProduct.Currency = product[headers["Currency"]];
uProduct.VAT = int.TryParse(product[headers["VAT"]], out int vat) ? vat : 0;
uProduct.VendorProductId = product[headers["VendorProductId"]];
uProduct.ManufacturerProductId = product[headers["ManufacturerProductId"]];
uProduct.PriceSuggestion = double.TryParse(product[headers["PriceSuggestion"]], out double priceSuggestion) ? priceSuggestion : 0;
uProduct.ProductStatusName = product[headers["ProductStatusName"]];
uProduct.PackedWeight = double.TryParse(product[headers["PackedWeight"]], out double packedWeight) ? packedWeight : 0;
uProduct.ModifiedOnUTC = DateTime.TryParse(product[headers["ModifiedOn"]], out DateTime modifiedOn) ? (Nullable <DateTime>)modifiedOn : null;
context.UProducts.Add(uProduct);
context.SaveChanges();
}
}
}
}