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