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