Example #1
0
        /// <summary>
        /// Import customer list from XLS file
        /// </summary>
        /// <param name="filePath">Excel file path</param>
        public static void ImportCustomersFromXls(string filePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(filePath))
            {
                excelHelper.Hdr  = "YES";
                excelHelper.Imex = "1";

                DataTable dt = excelHelper.ReadTable("Customers");
                foreach (DataRow dr in dt.Rows)
                {
                    int      customerId            = Convert.ToInt32(dr["CustomerId"]);
                    Guid     customerGuid          = new Guid(dr["CustomerGuid"].ToString());
                    string   email                 = dr["Email"].ToString();
                    string   username              = dr["Username"].ToString();
                    string   passwordHash          = dr["PasswordHash"].ToString();
                    string   saltKey               = dr["SaltKey"].ToString();
                    int      affiliateId           = Convert.ToInt32(dr["AffiliateId"]);
                    int      billingAddressId      = 0;
                    int      shippingAddressId     = 0;
                    int      lastPaymentMethodId   = 0;
                    string   lastAppliedCouponCode = string.Empty;
                    int      languageId            = Convert.ToInt32(dr["LanguageId"]);
                    int      currencyId            = Convert.ToInt32(dr["CurrencyId"]);
                    int      taxDisplayTypeId      = Convert.ToInt32(dr["TaxDisplayTypeId"]);
                    bool     isTaxExempt           = Convert.ToBoolean(dr["IsTaxExempt"]);
                    bool     isAdmin               = Convert.ToBoolean(dr["IsAdmin"]);
                    bool     isGuest               = Convert.ToBoolean(dr["IsGuest"]);
                    bool     isForumModerator      = Convert.ToBoolean(dr["IsForumModerator"]);
                    int      totalForumPosts       = Convert.ToInt32(dr["TotalForumPosts"]);
                    string   signature             = dr["Signature"].ToString();
                    string   adminComment          = dr["AdminComment"].ToString();
                    bool     active                = Convert.ToBoolean(dr["Active"]);
                    bool     deleted               = Convert.ToBoolean(dr["Deleted"]);
                    DateTime registrationDate      = DateTime.FromOADate(Convert.ToDouble(dr["RegistrationDate"]));
                    string   timeZoneId            = dr["TimeZoneId"].ToString();
                    int      avatarId              = Convert.ToInt32(dr["AvatarId"]);

                    //custom properties
                    string gender            = dr["Gender"].ToString();
                    string firstName         = dr["FirstName"].ToString();
                    string lastName          = dr["LastName"].ToString();
                    string company           = dr["Company"].ToString();
                    string streetAddress     = dr["StreetAddress"].ToString();
                    string streetAddress2    = dr["StreetAddress2"].ToString();
                    string zipPostalCode     = dr["ZipPostalCode"].ToString();
                    string city              = dr["City"].ToString();
                    string phoneNumber       = dr["PhoneNumber"].ToString();
                    string faxNumber         = dr["FaxNumber"].ToString();
                    int    countryId         = Convert.ToInt32(dr["CountryId"]);
                    int    stateProvinceId   = Convert.ToInt32(dr["StateProvinceId"]);
                    bool   receiveNewsletter = Convert.ToBoolean(dr["ReceiveNewsletter"]);


                    var customer = CustomerManager.GetCustomerByEmail(email);
                    if (customer == null)
                    {
                        //no customers found
                        customer = CustomerManager.AddCustomerForced(customerGuid, email, username,
                                                                     passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId,
                                                                     lastAppliedCouponCode, string.Empty, string.Empty,
                                                                     languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt,
                                                                     isAdmin, isGuest, isForumModerator, totalForumPosts, signature,
                                                                     adminComment, active, deleted, registrationDate, timeZoneId, avatarId);
                    }
                    else
                    {
                        if (!customer.IsGuest)
                        {
                            //customer is not a guest
                            customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid,
                                                                      email, username, passwordHash, saltKey, affiliateId, billingAddressId,
                                                                      shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode,
                                                                      string.Empty, string.Empty, languageId, currencyId,
                                                                      (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest,
                                                                      isForumModerator, totalForumPosts, signature, adminComment,
                                                                      active, deleted, registrationDate, timeZoneId, avatarId);
                        }
                        else
                        {
                            //customer is a guest
                            customer = CustomerManager.GetCustomerByGuid(customerGuid);
                            if (customer == null)
                            {
                                customer = CustomerManager.AddCustomerForced(customerGuid, email, username,
                                                                             passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId,
                                                                             lastAppliedCouponCode, string.Empty,
                                                                             string.Empty, languageId, currencyId,
                                                                             (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt,
                                                                             isAdmin, isGuest, isForumModerator, totalForumPosts, signature,
                                                                             adminComment, active, deleted, registrationDate, timeZoneId, avatarId);
                            }
                            else
                            {
                                customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid,
                                                                          email, username, passwordHash, saltKey, affiliateId, billingAddressId,
                                                                          shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode,
                                                                          string.Empty, string.Empty, languageId, currencyId,
                                                                          (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest,
                                                                          isForumModerator, totalForumPosts, signature, adminComment,
                                                                          active, deleted, registrationDate, timeZoneId, avatarId);
                            }
                        }
                    }

                    if (CustomerManager.FormFieldGenderEnabled)
                    {
                        customer.Gender = gender;
                    }
                    customer.FirstName = firstName;
                    customer.LastName  = lastName;
                    if (CustomerManager.FormFieldCompanyEnabled)
                    {
                        customer.Company = company;
                    }
                    if (CustomerManager.FormFieldStreetAddressEnabled)
                    {
                        customer.StreetAddress = streetAddress;
                    }
                    if (CustomerManager.FormFieldStreetAddress2Enabled)
                    {
                        customer.StreetAddress2 = streetAddress2;
                    }
                    if (CustomerManager.FormFieldPostCodeEnabled)
                    {
                        customer.ZipPostalCode = zipPostalCode;
                    }
                    if (CustomerManager.FormFieldCityEnabled)
                    {
                        customer.City = city;
                    }
                    if (CustomerManager.FormFieldPhoneEnabled)
                    {
                        customer.PhoneNumber = phoneNumber;
                    }
                    if (CustomerManager.FormFieldFaxEnabled)
                    {
                        customer.FaxNumber = faxNumber;
                    }
                    if (CustomerManager.FormFieldCountryEnabled)
                    {
                        customer.CountryId = countryId;
                    }
                    if (CustomerManager.FormFieldStateEnabled)
                    {
                        customer.StateProvinceId = stateProvinceId;
                    }
                    customer.ReceiveNewsletter = receiveNewsletter;
                }
            }
        }
        /// <summary>
        /// Import products from XLS file
        /// </summary>
        /// <param name="FilePath">Excel file path</param>
        public static void ImportProductsFromXLS(string FilePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(FilePath))
            {
                excelHelper.HDR = "YES";
                excelHelper.IMEX = "1";

                DataTable dt = excelHelper.ReadTable("Products");
                foreach (DataRow dr in dt.Rows)
                {
                    string Name = dr["Name"].ToString();
                    string ShortDescription = dr["ShortDescription"].ToString();
                    string FullDescription = dr["FullDescription"].ToString();
                    int ProductTypeID = Convert.ToInt32(dr["ProductTypeID"]);
                    int TemplateID = Convert.ToInt32(dr["TemplateID"]);
                    bool ShowOnHomePage = Convert.ToBoolean(dr["ShowOnHomePage"]);
                    string MetaKeywords = dr["MetaKeywords"].ToString();
                    string MetaDescription = dr["MetaDescription"].ToString();
                    string MetaTitle = dr["MetaTitle"].ToString();
                    bool AllowCustomerReviews = Convert.ToBoolean(dr["AllowCustomerReviews"]);
                    bool AllowCustomerRatings = Convert.ToBoolean(dr["AllowCustomerRatings"]);
                    bool Published = Convert.ToBoolean(dr["Published"]);
                    string SKU = dr["SKU"].ToString();
                    string ManufacturerPartNumber = dr["ManufacturerPartNumber"].ToString();
                    bool IsDownload = Convert.ToBoolean(dr["IsDownload"]);
                    int DownloadID = Convert.ToInt32(dr["DownloadID"]);
                    bool UnlimitedDownloads = Convert.ToBoolean(dr["UnlimitedDownloads"]);
                    int MaxNumberOfDownloads = Convert.ToInt32(dr["MaxNumberOfDownloads"]);
                    bool HasSampleDownload = Convert.ToBoolean(dr["HasSampleDownload"]);
                    int SampleDownloadID = Convert.ToInt32(dr["SampleDownloadID"]);
                    bool IsShipEnabled = Convert.ToBoolean(dr["IsShipEnabled"]);
                    bool IsFreeShipping = Convert.ToBoolean(dr["IsFreeShipping"]);
                    decimal AdditionalShippingCharge = Convert.ToDecimal(dr["AdditionalShippingCharge"]);
                    bool IsTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]);
                    int TaxCategoryID = Convert.ToInt32(dr["TaxCategoryID"]);
                    bool ManageInventory = Convert.ToBoolean(dr["ManageInventory"]);
                    int StockQuantity = Convert.ToInt32(dr["StockQuantity"]);
                    int MinStockQuantity = Convert.ToInt32(dr["MinStockQuantity"]);
                    int LowStockActivityID = Convert.ToInt32(dr["LowStockActivityID"]);
                    int NotifyAdminForQuantityBelow = Convert.ToInt32(dr["NotifyAdminForQuantityBelow"]);
                    int OrderMinimumQuantity = Convert.ToInt32(dr["OrderMinimumQuantity"]);
                    int OrderMaximumQuantity = Convert.ToInt32(dr["OrderMaximumQuantity"]);
                    bool DisableBuyButton = Convert.ToBoolean(dr["DisableBuyButton"]);
                    decimal Price = Convert.ToDecimal(dr["Price"]);
                    decimal OldPrice = Convert.ToDecimal(dr["OldPrice"]);
                    decimal Weight = Convert.ToDecimal(dr["Weight"]);
                    decimal Length = Convert.ToDecimal(dr["Length"]);
                    decimal Width = Convert.ToDecimal(dr["Width"]);
                    decimal Height = Convert.ToDecimal(dr["Height"]);
                    DateTime CreatedOn = Convert.ToDateTime(dr["CreatedOn"]);

                    Product product = ProductManager.InsertProduct(Name, ShortDescription, FullDescription,
                        string.Empty, ProductTypeID, TemplateID, ShowOnHomePage, MetaKeywords, MetaDescription,
                        MetaTitle, string.Empty, AllowCustomerReviews, AllowCustomerRatings, 0, 0,
                        Published, false, CreatedOn, DateTime.Now);

                    ProductVariant productVariant = ProductManager.InsertProductVariant(product.ProductID,
                        string.Empty, SKU, string.Empty, string.Empty, ManufacturerPartNumber, IsDownload, DownloadID,
                        UnlimitedDownloads, MaxNumberOfDownloads, HasSampleDownload, SampleDownloadID,
                        IsShipEnabled, IsFreeShipping, AdditionalShippingCharge, IsTaxExempt,
                        TaxCategoryID, ManageInventory, StockQuantity, MinStockQuantity,
                        (LowStockActivityEnum)LowStockActivityID, NotifyAdminForQuantityBelow,
                        OrderMinimumQuantity, OrderMaximumQuantity, 0, DisableBuyButton, Price, OldPrice, Weight, Length, Width, Height, 0, null, null,
                        true, false, 1, CreatedOn, DateTime.Now);
                }
            }
        }
Example #3
0
        /// <summary>
        /// Import products from XLS file
        /// </summary>
        /// <param name="filePath">Excel file path</param>
        public static void ImportProductsFromXls(string filePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(filePath))
            {
                excelHelper.Hdr  = "YES";
                excelHelper.Imex = "1";

                DataTable dt = excelHelper.ReadTable("Products");
                foreach (DataRow dr in dt.Rows)
                {
                    string   Name                 = dr["Name"].ToString();
                    string   ShortDescription     = dr["ShortDescription"].ToString();
                    string   FullDescription      = dr["FullDescription"].ToString();
                    int      ProductTypeId        = Convert.ToInt32(dr["ProductTypeId"]);
                    int      TemplateId           = Convert.ToInt32(dr["TemplateId"]);
                    bool     ShowOnHomePage       = Convert.ToBoolean(dr["ShowOnHomePage"]);
                    string   MetaKeywords         = dr["MetaKeywords"].ToString();
                    string   MetaDescription      = dr["MetaDescription"].ToString();
                    string   MetaTitle            = dr["MetaTitle"].ToString();
                    bool     AllowCustomerReviews = Convert.ToBoolean(dr["AllowCustomerReviews"]);
                    bool     AllowCustomerRatings = Convert.ToBoolean(dr["AllowCustomerRatings"]);
                    bool     Published            = Convert.ToBoolean(dr["Published"]);
                    string   SKU = dr["SKU"].ToString();
                    string   ManufacturerPartNumber      = dr["ManufacturerPartNumber"].ToString();
                    bool     IsGiftCard                  = Convert.ToBoolean(dr["IsGiftCard"]);
                    bool     AllowYandexMarket           = Convert.ToBoolean(dr["AllowYandexMarket"]);
                    bool     IsDownload                  = Convert.ToBoolean(dr["IsDownload"]);
                    int      DownloadId                  = Convert.ToInt32(dr["DownloadId"]);
                    bool     UnlimitedDownloads          = Convert.ToBoolean(dr["UnlimitedDownloads"]);
                    int      MaxNumberOfDownloads        = Convert.ToInt32(dr["MaxNumberOfDownloads"]);
                    bool     HasSampleDownload           = Convert.ToBoolean(dr["HasSampleDownload"]);
                    int      DownloadActivationType      = Convert.ToInt32(dr["DownloadActivationType"]);
                    int      SampleDownloadId            = Convert.ToInt32(dr["SampleDownloadId"]);
                    bool     HasUserAgreement            = Convert.ToBoolean(dr["HasUserAgreement"]);
                    string   UserAgreementText           = dr["UserAgreementText"].ToString();
                    bool     IsRecurring                 = Convert.ToBoolean(dr["IsRecurring"]);
                    int      CycleLength                 = Convert.ToInt32(dr["CycleLength"]);
                    int      CyclePeriod                 = Convert.ToInt32(dr["CyclePeriod"]);
                    int      TotalCycles                 = Convert.ToInt32(dr["TotalCycles"]);
                    bool     IsShipEnabled               = Convert.ToBoolean(dr["IsShipEnabled"]);
                    bool     IsFreeShipping              = Convert.ToBoolean(dr["IsFreeShipping"]);
                    decimal  AdditionalShippingCharge    = Convert.ToDecimal(dr["AdditionalShippingCharge"]);
                    bool     IsTaxExempt                 = Convert.ToBoolean(dr["IsTaxExempt"]);
                    int      TaxCategoryId               = Convert.ToInt32(dr["TaxCategoryId"]);
                    int      ManageInventory             = Convert.ToInt32(dr["ManageInventory"]);
                    int      StockQuantity               = Convert.ToInt32(dr["StockQuantity"]);
                    bool     DisplayStockAvailability    = Convert.ToBoolean(dr["DisplayStockAvailability"]);
                    int      MinStockQuantity            = Convert.ToInt32(dr["MinStockQuantity"]);
                    int      LowStockActivityId          = Convert.ToInt32(dr["LowStockActivityId"]);
                    int      NotifyAdminForQuantityBelow = Convert.ToInt32(dr["NotifyAdminForQuantityBelow"]);
                    bool     AllowOutOfStockOrders       = Convert.ToBoolean(dr["AllowOutOfStockOrders"]);
                    int      OrderMinimumQuantity        = Convert.ToInt32(dr["OrderMinimumQuantity"]);
                    int      OrderMaximumQuantity        = Convert.ToInt32(dr["OrderMaximumQuantity"]);
                    bool     DisableBuyButton            = Convert.ToBoolean(dr["DisableBuyButton"]);
                    decimal  Price                       = Convert.ToDecimal(dr["Price"]);
                    decimal  OldPrice                    = Convert.ToDecimal(dr["OldPrice"]);
                    decimal  ProductCost                 = Convert.ToDecimal(dr["ProductCost"]);
                    bool     CustomerEntersPrice         = Convert.ToBoolean(dr["CustomerEntersPrice"]);
                    decimal  MinimumCustomerEnteredPrice = Convert.ToDecimal(dr["MinimumCustomerEnteredPrice"]);
                    decimal  MaximumCustomerEnteredPrice = Convert.ToDecimal(dr["MaximumCustomerEnteredPrice"]);
                    decimal  Weight                      = Convert.ToDecimal(dr["Weight"]);
                    decimal  Length                      = Convert.ToDecimal(dr["Length"]);
                    decimal  Width                       = Convert.ToDecimal(dr["Width"]);
                    decimal  Height                      = Convert.ToDecimal(dr["Height"]);
                    DateTime CreatedOn                   = DateTime.FromOADate(Convert.ToDouble(dr["CreatedOn"]));

                    var productVariant = ProductManager.GetProductVariantBySKU(SKU);
                    if (productVariant != null)
                    {
                        var product = ProductManager.GetProductById(productVariant.ProductId, 0);
                        product = ProductManager.UpdateProduct(product.ProductId, Name, ShortDescription,
                                                               FullDescription, product.AdminComment, ProductTypeId,
                                                               TemplateId, ShowOnHomePage, MetaKeywords, MetaDescription,
                                                               MetaTitle, product.SEName, AllowCustomerReviews, AllowCustomerRatings,
                                                               product.RatingSum, product.TotalRatingVotes,
                                                               Published, product.Deleted, CreatedOn, DateTime.Now);

                        productVariant = ProductManager.UpdateProductVariant(productVariant.ProductVariantId,
                                                                             productVariant.ProductId, productVariant.Name, SKU,
                                                                             productVariant.Description, productVariant.AdminComment,
                                                                             ManufacturerPartNumber, IsGiftCard, AllowYandexMarket, IsDownload, DownloadId,
                                                                             UnlimitedDownloads, MaxNumberOfDownloads, productVariant.DownloadExpirationDays,
                                                                             (DownloadActivationTypeEnum)DownloadActivationType, HasSampleDownload,
                                                                             SampleDownloadId, HasUserAgreement, UserAgreementText, IsRecurring,
                                                                             CycleLength, CyclePeriod, TotalCycles, IsShipEnabled,
                                                                             IsFreeShipping, AdditionalShippingCharge, IsTaxExempt,
                                                                             TaxCategoryId, ManageInventory, StockQuantity,
                                                                             DisplayStockAvailability, MinStockQuantity,
                                                                             (LowStockActivityEnum)LowStockActivityId, NotifyAdminForQuantityBelow,
                                                                             AllowOutOfStockOrders, OrderMinimumQuantity,
                                                                             OrderMaximumQuantity, productVariant.WarehouseId, DisableBuyButton,
                                                                             Price, OldPrice, ProductCost, CustomerEntersPrice,
                                                                             MinimumCustomerEnteredPrice, MaximumCustomerEnteredPrice,
                                                                             Weight, Length, Width, Height,
                                                                             productVariant.PictureId, productVariant.AvailableStartDateTime,
                                                                             productVariant.AvailableEndDateTime, productVariant.Published,
                                                                             productVariant.Deleted, productVariant.DisplayOrder, CreatedOn, DateTime.Now);
                    }
                    else
                    {
                        var product = ProductManager.InsertProduct(Name, ShortDescription, FullDescription,
                                                                   string.Empty, ProductTypeId, TemplateId, ShowOnHomePage, MetaKeywords, MetaDescription,
                                                                   MetaTitle, string.Empty, AllowCustomerReviews, AllowCustomerRatings, 0, 0,
                                                                   Published, false, CreatedOn, DateTime.Now);

                        productVariant = ProductManager.InsertProductVariant(product.ProductId,
                                                                             string.Empty, SKU, string.Empty, string.Empty, ManufacturerPartNumber,
                                                                             IsGiftCard, AllowYandexMarket, IsDownload, DownloadId,
                                                                             UnlimitedDownloads, MaxNumberOfDownloads, null, (DownloadActivationTypeEnum)DownloadActivationType,
                                                                             HasSampleDownload, SampleDownloadId, HasUserAgreement, UserAgreementText, IsRecurring, CycleLength, CyclePeriod, TotalCycles,
                                                                             IsShipEnabled, IsFreeShipping, AdditionalShippingCharge, IsTaxExempt,
                                                                             TaxCategoryId, ManageInventory, StockQuantity, DisplayStockAvailability, MinStockQuantity,
                                                                             (LowStockActivityEnum)LowStockActivityId, NotifyAdminForQuantityBelow,
                                                                             AllowOutOfStockOrders, OrderMinimumQuantity,
                                                                             OrderMaximumQuantity, 0, DisableBuyButton,
                                                                             Price, OldPrice, ProductCost, CustomerEntersPrice,
                                                                             MinimumCustomerEnteredPrice, MaximumCustomerEnteredPrice,
                                                                             Weight, Length, Width, Height, 0, null, null,
                                                                             true, false, 1, CreatedOn, DateTime.Now);
                    }
                }
            }
        }
        /// <summary>
        /// Import customer list from XLS file
        /// </summary>
        /// <param name="FilePath">Excel file path</param>
        public static void ImportCustomersFromXLS(string FilePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(FilePath))
            {
                excelHelper.HDR = "YES";
                excelHelper.IMEX = "1";

                DataTable dt = excelHelper.ReadTable("Customers");
                foreach (DataRow dr in dt.Rows)
                {
                    int customerID = Convert.ToInt32(dr["CustomerID"]);
                    Guid customerGUID = new Guid(dr["CustomerGUID"].ToString());
                    string email = dr["Email"].ToString();
                    string username = dr["Username"].ToString();
                    string passwordHash = dr["PasswordHash"].ToString();
                    string saltKey = dr["SaltKey"].ToString();
                    int affiliateID = Convert.ToInt32(dr["AffiliateID"]);
                    int billingAddressID = Convert.ToInt32(dr["BillingAddressID"]);
                    int shippingAddressID = Convert.ToInt32(dr["ShippingAddressID"]);
                    int lastPaymentMethodID = Convert.ToInt32(dr["LastPaymentMethodID"]);
                    string lastAppliedCouponCode = dr["LastAppliedCouponCode"].ToString();
                    int languageID = Convert.ToInt32(dr["LanguageID"]);
                    int currencyID = Convert.ToInt32(dr["CurrencyID"]);
                    int taxDisplayTypeID = Convert.ToInt32(dr["TaxDisplayTypeID"]);
                    bool isTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]);
                    bool isAdmin = Convert.ToBoolean(dr["IsAdmin"]);
                    bool isGuest = Convert.ToBoolean(dr["IsGuest"]);
                    bool isForumModerator = Convert.ToBoolean(dr["IsForumModerator"]);
                    int totalForumPosts = Convert.ToInt32(dr["TotalForumPosts"]);
                    string signature = dr["Signature"].ToString();
                    string adminComment = dr["AdminComment"].ToString();
                    bool active = Convert.ToBoolean(dr["Active"]);
                    bool deleted = Convert.ToBoolean(dr["Deleted"]);
                    DateTime registrationDate = Convert.ToDateTime(dr["RegistrationDate"]);
                    string timeZoneID = dr["TimeZoneID"].ToString();
                    int avatarID = Convert.ToInt32(dr["AvatarID"]);

                    //custom properties
                    string gender = dr["Gender"].ToString();
                    string firstName = dr["FirstName"].ToString();
                    string lastName = dr["LastName"].ToString();
                    string company = dr["Company"].ToString();
                    string streetAddress = dr["StreetAddress"].ToString();
                    string streetAddress2 = dr["StreetAddress2"].ToString();
                    string zipPostalCode = dr["ZipPostalCode"].ToString();
                    string city = dr["City"].ToString();
                    string phoneNumber = dr["PhoneNumber"].ToString();
                    string faxNumber = dr["FaxNumber"].ToString();
                    int countryID = Convert.ToInt32(dr["CountryID"]);
                    int stateProvinceID = Convert.ToInt32(dr["StateProvinceID"]);
                    bool receiveNewsletter = Convert.ToBoolean(dr["ReceiveNewsletter"]);


                    Customer customer = CustomerManager.GetCustomerByEmail(email);
                    if (customer == null)
                    {
                        //no customers found
                        customer = CustomerManager.AddCustomerForced(customerGUID, email, username,
                            passwordHash, saltKey, affiliateID, billingAddressID, shippingAddressID, lastPaymentMethodID,
                            lastAppliedCouponCode, languageID, currencyID, (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt,
                            isAdmin, isGuest, isForumModerator, totalForumPosts, signature,
                            adminComment, active, deleted, registrationDate, timeZoneID, avatarID);
                    }
                    else
                    {
                        if (!customer.IsGuest)
                        {
                            //customer is not a guest
                            customer = CustomerManager.UpdateCustomer(customer.CustomerID, customer.CustomerGUID,
                                email, username, passwordHash, saltKey, affiliateID, billingAddressID,
                                shippingAddressID, lastPaymentMethodID, lastAppliedCouponCode, languageID, currencyID,
                                (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt, isAdmin, isGuest,
                                isForumModerator, totalForumPosts, signature, adminComment,
                                active, deleted, registrationDate, timeZoneID, avatarID);
                        }
                        else
                        {
                            //customer is a guest
                            customer = CustomerManager.GetCustomerByGUID(customerGUID);
                            if (customer == null)
                            {
                                customer = CustomerManager.AddCustomerForced(customerGUID, email, username,
                                    passwordHash, saltKey, affiliateID, billingAddressID, shippingAddressID, lastPaymentMethodID,
                                    lastAppliedCouponCode, languageID, currencyID, (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt,
                                    isAdmin, isGuest, isForumModerator, totalForumPosts, signature,
                                    adminComment, active, deleted, registrationDate, timeZoneID, avatarID);
                            }
                            else
                            {
                                customer = CustomerManager.UpdateCustomer(customer.CustomerID, customer.CustomerGUID,
                                    email, username, passwordHash, saltKey, affiliateID, billingAddressID,
                                    shippingAddressID, lastPaymentMethodID, lastAppliedCouponCode, languageID, currencyID,
                                    (TaxDisplayTypeEnum)taxDisplayTypeID, isTaxExempt, isAdmin, isGuest,
                                    isForumModerator, totalForumPosts, signature, adminComment,
                                    active, deleted, registrationDate, timeZoneID, avatarID);
                            }
                        }
                    }
                    customer.Gender = gender;
                    customer.FirstName = firstName;
                    customer.LastName = lastName;
                    customer.Company = company;
                    customer.StreetAddress = streetAddress;
                    customer.StreetAddress2 = streetAddress2;
                    customer.ZipPostalCode = zipPostalCode;
                    customer.City = city;
                    customer.PhoneNumber = phoneNumber;
                    customer.FaxNumber = faxNumber;
                    customer.CountryID = countryID;
                    customer.StateProvinceID = stateProvinceID;
                    customer.ReceiveNewsletter = receiveNewsletter;
                }
            }
        }
        /// <summary>
        /// Import products from XLS file
        /// </summary>
        /// <param name="filePath">Excel file path</param>
        public void ImportProductsFromXls(string filePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(filePath))
            {
                excelHelper.Hdr  = "YES";
                excelHelper.Imex = "1";

                DataTable dt = excelHelper.ReadTable("Products");
                foreach (DataRow dr in dt.Rows)
                {
                    string   Name                 = dr["Name"].ToString();
                    string   ShortDescription     = dr["ShortDescription"].ToString();
                    string   FullDescription      = dr["FullDescription"].ToString();
                    int      TemplateId           = Convert.ToInt32(dr["TemplateId"]);
                    bool     ShowOnHomePage       = Convert.ToBoolean(dr["ShowOnHomePage"]);
                    string   MetaKeywords         = dr["MetaKeywords"].ToString();
                    string   MetaDescription      = dr["MetaDescription"].ToString();
                    string   MetaTitle            = dr["MetaTitle"].ToString();
                    bool     AllowCustomerReviews = Convert.ToBoolean(dr["AllowCustomerReviews"]);
                    bool     AllowCustomerRatings = Convert.ToBoolean(dr["AllowCustomerRatings"]);
                    bool     Published            = Convert.ToBoolean(dr["Published"]);
                    string   SKU = dr["SKU"].ToString();
                    string   ManufacturerPartNumber      = dr["ManufacturerPartNumber"].ToString();
                    bool     IsGiftCard                  = Convert.ToBoolean(dr["IsGiftCard"]);
                    int      GiftCardType                = Convert.ToInt32(dr["GiftCardType"]);
                    bool     IsDownload                  = Convert.ToBoolean(dr["IsDownload"]);
                    int      DownloadId                  = Convert.ToInt32(dr["DownloadId"]);
                    bool     UnlimitedDownloads          = Convert.ToBoolean(dr["UnlimitedDownloads"]);
                    int      MaxNumberOfDownloads        = Convert.ToInt32(dr["MaxNumberOfDownloads"]);
                    bool     HasSampleDownload           = Convert.ToBoolean(dr["HasSampleDownload"]);
                    int      DownloadActivationType      = Convert.ToInt32(dr["DownloadActivationType"]);
                    int      SampleDownloadId            = Convert.ToInt32(dr["SampleDownloadId"]);
                    bool     HasUserAgreement            = Convert.ToBoolean(dr["HasUserAgreement"]);
                    string   UserAgreementText           = dr["UserAgreementText"].ToString();
                    bool     IsRecurring                 = Convert.ToBoolean(dr["IsRecurring"]);
                    int      CycleLength                 = Convert.ToInt32(dr["CycleLength"]);
                    int      CyclePeriod                 = Convert.ToInt32(dr["CyclePeriod"]);
                    int      TotalCycles                 = Convert.ToInt32(dr["TotalCycles"]);
                    bool     IsShipEnabled               = Convert.ToBoolean(dr["IsShipEnabled"]);
                    bool     IsFreeShipping              = Convert.ToBoolean(dr["IsFreeShipping"]);
                    decimal  AdditionalShippingCharge    = Convert.ToDecimal(dr["AdditionalShippingCharge"]);
                    bool     IsTaxExempt                 = Convert.ToBoolean(dr["IsTaxExempt"]);
                    int      TaxCategoryId               = Convert.ToInt32(dr["TaxCategoryId"]);
                    int      ManageInventory             = Convert.ToInt32(dr["ManageInventory"]);
                    int      StockQuantity               = Convert.ToInt32(dr["StockQuantity"]);
                    bool     DisplayStockAvailability    = Convert.ToBoolean(dr["DisplayStockAvailability"]);
                    bool     DisplayStockQuantity        = Convert.ToBoolean(dr["DisplayStockQuantity"]);
                    int      MinStockQuantity            = Convert.ToInt32(dr["MinStockQuantity"]);
                    int      LowStockActivityId          = Convert.ToInt32(dr["LowStockActivityId"]);
                    int      NotifyAdminForQuantityBelow = Convert.ToInt32(dr["NotifyAdminForQuantityBelow"]);
                    int      Backorders                  = Convert.ToInt32(dr["Backorders"]);
                    int      OrderMinimumQuantity        = Convert.ToInt32(dr["OrderMinimumQuantity"]);
                    int      OrderMaximumQuantity        = Convert.ToInt32(dr["OrderMaximumQuantity"]);
                    bool     DisableBuyButton            = Convert.ToBoolean(dr["DisableBuyButton"]);
                    bool     CallForPrice                = Convert.ToBoolean(dr["CallForPrice"]);
                    decimal  Price                       = Convert.ToDecimal(dr["Price"]);
                    decimal  OldPrice                    = Convert.ToDecimal(dr["OldPrice"]);
                    decimal  ProductCost                 = Convert.ToDecimal(dr["ProductCost"]);
                    bool     CustomerEntersPrice         = Convert.ToBoolean(dr["CustomerEntersPrice"]);
                    decimal  MinimumCustomerEnteredPrice = Convert.ToDecimal(dr["MinimumCustomerEnteredPrice"]);
                    decimal  MaximumCustomerEnteredPrice = Convert.ToDecimal(dr["MaximumCustomerEnteredPrice"]);
                    decimal  Weight                      = Convert.ToDecimal(dr["Weight"]);
                    decimal  Length                      = Convert.ToDecimal(dr["Length"]);
                    decimal  Width                       = Convert.ToDecimal(dr["Width"]);
                    decimal  Height                      = Convert.ToDecimal(dr["Height"]);
                    DateTime CreatedOn                   = DateTime.FromOADate(Convert.ToDouble(dr["CreatedOn"]));

                    var productVariant = IoC.Resolve <IProductService>().GetProductVariantBySKU(SKU);
                    if (productVariant != null)
                    {
                        var product = IoC.Resolve <IProductService>().GetProductById(productVariant.ProductId);
                        product.Name                 = Name;
                        product.ShortDescription     = ShortDescription;
                        product.FullDescription      = FullDescription;
                        product.TemplateId           = TemplateId;
                        product.ShowOnHomePage       = ShowOnHomePage;
                        product.MetaKeywords         = MetaKeywords;
                        product.MetaDescription      = MetaDescription;
                        product.MetaTitle            = MetaTitle;
                        product.AllowCustomerReviews = AllowCustomerReviews;
                        product.AllowCustomerRatings = AllowCustomerRatings;
                        product.Published            = Published;
                        product.CreatedOn            = CreatedOn;
                        product.UpdatedOn            = DateTime.UtcNow;

                        IoC.Resolve <IProductService>().UpdateProduct(product);

                        productVariant.SKU = SKU;
                        productVariant.ManufacturerPartNumber      = ManufacturerPartNumber;
                        productVariant.IsGiftCard                  = IsGiftCard;
                        productVariant.GiftCardType                = GiftCardType;
                        productVariant.IsDownload                  = IsDownload;
                        productVariant.DownloadId                  = DownloadId;
                        productVariant.UnlimitedDownloads          = UnlimitedDownloads;
                        productVariant.MaxNumberOfDownloads        = MaxNumberOfDownloads;
                        productVariant.DownloadActivationType      = DownloadActivationType;
                        productVariant.HasSampleDownload           = HasSampleDownload;
                        productVariant.SampleDownloadId            = SampleDownloadId;
                        productVariant.HasUserAgreement            = HasUserAgreement;
                        productVariant.UserAgreementText           = UserAgreementText;
                        productVariant.IsRecurring                 = IsRecurring;
                        productVariant.CycleLength                 = CycleLength;
                        productVariant.CyclePeriod                 = CyclePeriod;
                        productVariant.TotalCycles                 = TotalCycles;
                        productVariant.IsShipEnabled               = IsShipEnabled;
                        productVariant.IsFreeShipping              = IsFreeShipping;
                        productVariant.AdditionalShippingCharge    = AdditionalShippingCharge;
                        productVariant.IsTaxExempt                 = IsTaxExempt;
                        productVariant.TaxCategoryId               = TaxCategoryId;
                        productVariant.ManageInventory             = ManageInventory;
                        productVariant.StockQuantity               = StockQuantity;
                        productVariant.DisplayStockAvailability    = DisplayStockAvailability;
                        productVariant.DisplayStockQuantity        = DisplayStockQuantity;
                        productVariant.MinStockQuantity            = MinStockQuantity;
                        productVariant.LowStockActivityId          = LowStockActivityId;
                        productVariant.NotifyAdminForQuantityBelow = NotifyAdminForQuantityBelow;
                        productVariant.Backorders                  = Backorders;
                        productVariant.OrderMinimumQuantity        = OrderMinimumQuantity;
                        productVariant.OrderMaximumQuantity        = OrderMaximumQuantity;
                        productVariant.DisableBuyButton            = DisableBuyButton;
                        productVariant.CallForPrice                = CallForPrice;
                        productVariant.Price                       = Price;
                        productVariant.OldPrice                    = OldPrice;
                        productVariant.ProductCost                 = ProductCost;
                        productVariant.CustomerEntersPrice         = CustomerEntersPrice;
                        productVariant.MinimumCustomerEnteredPrice = MinimumCustomerEnteredPrice;
                        productVariant.MaximumCustomerEnteredPrice = MaximumCustomerEnteredPrice;
                        productVariant.Weight                      = Weight;
                        productVariant.Length                      = Length;
                        productVariant.Width                       = Width;
                        productVariant.Height                      = Height;
                        productVariant.Published                   = Published;
                        productVariant.CreatedOn                   = CreatedOn;
                        productVariant.UpdatedOn                   = DateTime.UtcNow;

                        IoC.Resolve <IProductService>().UpdateProductVariant(productVariant);
                    }
                    else
                    {
                        var product = new Product()
                        {
                            Name                 = Name,
                            ShortDescription     = ShortDescription,
                            FullDescription      = FullDescription,
                            TemplateId           = TemplateId,
                            ShowOnHomePage       = ShowOnHomePage,
                            MetaKeywords         = MetaKeywords,
                            MetaDescription      = MetaDescription,
                            MetaTitle            = MetaTitle,
                            AllowCustomerReviews = AllowCustomerReviews,
                            AllowCustomerRatings = AllowCustomerRatings,
                            Published            = Published,
                            CreatedOn            = CreatedOn,
                            UpdatedOn            = DateTime.UtcNow
                        };
                        IoC.Resolve <IProductService>().InsertProduct(product);

                        productVariant = new ProductVariant()
                        {
                            ProductId = product.ProductId,
                            SKU       = SKU,
                            ManufacturerPartNumber      = ManufacturerPartNumber,
                            IsGiftCard                  = IsGiftCard,
                            GiftCardType                = GiftCardType,
                            IsDownload                  = IsDownload,
                            DownloadId                  = DownloadId,
                            UnlimitedDownloads          = UnlimitedDownloads,
                            MaxNumberOfDownloads        = MaxNumberOfDownloads,
                            DownloadActivationType      = DownloadActivationType,
                            HasSampleDownload           = HasSampleDownload,
                            SampleDownloadId            = SampleDownloadId,
                            HasUserAgreement            = HasUserAgreement,
                            UserAgreementText           = UserAgreementText,
                            IsRecurring                 = IsRecurring,
                            CycleLength                 = CycleLength,
                            CyclePeriod                 = CyclePeriod,
                            TotalCycles                 = TotalCycles,
                            IsShipEnabled               = IsShipEnabled,
                            IsFreeShipping              = IsFreeShipping,
                            AdditionalShippingCharge    = AdditionalShippingCharge,
                            IsTaxExempt                 = IsTaxExempt,
                            TaxCategoryId               = TaxCategoryId,
                            ManageInventory             = ManageInventory,
                            StockQuantity               = StockQuantity,
                            DisplayStockAvailability    = DisplayStockAvailability,
                            DisplayStockQuantity        = DisplayStockQuantity,
                            MinStockQuantity            = MinStockQuantity,
                            LowStockActivityId          = LowStockActivityId,
                            NotifyAdminForQuantityBelow = NotifyAdminForQuantityBelow,
                            Backorders                  = Backorders,
                            OrderMinimumQuantity        = OrderMinimumQuantity,
                            OrderMaximumQuantity        = OrderMaximumQuantity,
                            DisableBuyButton            = DisableBuyButton,
                            CallForPrice                = CallForPrice,
                            Price                       = Price,
                            OldPrice                    = OldPrice,
                            ProductCost                 = ProductCost,
                            CustomerEntersPrice         = CustomerEntersPrice,
                            MinimumCustomerEnteredPrice = MinimumCustomerEnteredPrice,
                            MaximumCustomerEnteredPrice = MaximumCustomerEnteredPrice,
                            Weight                      = Weight,
                            Length                      = Length,
                            Width                       = Width,
                            Height                      = Height,
                            Published                   = Published,
                            CreatedOn                   = CreatedOn,
                            UpdatedOn                   = DateTime.UtcNow
                        };

                        IoC.Resolve <IProductService>().InsertProductVariant(productVariant);
                    }
                }
            }
        }
Example #6
0
        /// <summary>
        /// Import customer list from XLS file
        /// </summary>
        /// <param name="filePath">Excel file path</param>
        public static void ImportCustomersFromXls(string filePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(filePath))
            {
                excelHelper.Hdr = "YES";
                excelHelper.Imex = "1";

                DataTable dt = excelHelper.ReadTable("Customers");
                foreach (DataRow dr in dt.Rows)
                {
                    int customerId = Convert.ToInt32(dr["CustomerId"]);
                    Guid customerGuid = new Guid(dr["CustomerGuid"].ToString());
                    string email = dr["Email"].ToString();
                    string username = dr["Username"].ToString();
                    string passwordHash = dr["PasswordHash"].ToString();
                    string saltKey = dr["SaltKey"].ToString();
                    int affiliateId = Convert.ToInt32(dr["AffiliateId"]);
                    int billingAddressId = 0;
                    int shippingAddressId = 0;
                    int lastPaymentMethodId = 0;
                    string lastAppliedCouponCode = string.Empty;
                    int languageId = Convert.ToInt32(dr["LanguageId"]);
                    int currencyId = Convert.ToInt32(dr["CurrencyId"]);
                    int taxDisplayTypeId = Convert.ToInt32(dr["TaxDisplayTypeId"]);
                    bool isTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]);
                    bool isAdmin = Convert.ToBoolean(dr["IsAdmin"]);
                    bool isGuest = Convert.ToBoolean(dr["IsGuest"]);
                    bool isForumModerator = Convert.ToBoolean(dr["IsForumModerator"]);
                    int totalForumPosts = Convert.ToInt32(dr["TotalForumPosts"]);
                    string signature = dr["Signature"].ToString();
                    string adminComment = dr["AdminComment"].ToString();
                    bool active = Convert.ToBoolean(dr["Active"]);
                    bool deleted = Convert.ToBoolean(dr["Deleted"]);
                    DateTime registrationDate = DateTime.FromOADate(Convert.ToDouble(dr["RegistrationDate"]));
                    string timeZoneId = dr["TimeZoneId"].ToString();
                    int avatarId = Convert.ToInt32(dr["AvatarId"]);

                    //custom properties
                    //string gender = dr["Gender"].ToString();
                    string title = dr["Title"].ToString();
                    string firstName = dr["FirstName"].ToString();
                    string lastName = dr["LastName"].ToString();
                    string company = dr["Company"].ToString();
                    string vatNumber = dr["VatNumber"].ToString();
                    VatNumberStatusEnum vatNumberStatus = (VatNumberStatusEnum)Convert.ToInt32(dr["VatNumberStatus"]);
                    string streetAddress = dr["StreetAddress"].ToString();
                    string streetAddress2 = dr["StreetAddress2"].ToString();
                    string zipPostalCode = dr["ZipPostalCode"].ToString();
                    string city = dr["City"].ToString();
                    string phoneNumber = dr["PhoneNumber"].ToString();
                    string faxNumber = dr["FaxNumber"].ToString();
                    int countryId = Convert.ToInt32(dr["CountryId"]);
                    int stateProvinceId = Convert.ToInt32(dr["StateProvinceId"]);
                    bool receiveNewsletter = Convert.ToBoolean(dr["ReceiveNewsletter"]);

                    var customer = CustomerManager.GetCustomerByEmail(email);
                    if (customer == null)
                    {
                        //no customers found
                        customer = CustomerManager.AddCustomerForced(customerGuid, email, username,
                            passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId,
                            lastAppliedCouponCode, string.Empty, string.Empty,
                            languageId, currencyId, (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt,
                            isAdmin, isGuest, isForumModerator, totalForumPosts, signature,
                            adminComment, active, deleted, registrationDate, timeZoneId, avatarId, null);
                    }
                    else
                    {
                        if (!customer.IsGuest)
                        {
                            //customer is not a guest
                            customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid,
                                email, username, passwordHash, saltKey, affiliateId, billingAddressId,
                                shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode,
                                string.Empty, string.Empty, languageId, currencyId,
                                (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest,
                                isForumModerator, totalForumPosts, signature, adminComment,
                                active, deleted, registrationDate, timeZoneId, avatarId, customer.DateOfBirth);
                        }
                        else
                        {
                            //customer is a guest
                            customer = CustomerManager.GetCustomerByGuid(customerGuid);
                            if (customer == null)
                            {
                                customer = CustomerManager.AddCustomerForced(customerGuid, email, username,
                                    passwordHash, saltKey, affiliateId, billingAddressId, shippingAddressId, lastPaymentMethodId,
                                    lastAppliedCouponCode, string.Empty,
                                    string.Empty, languageId, currencyId,
                                    (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt,
                                    isAdmin, isGuest, isForumModerator, totalForumPosts, signature,
                                    adminComment, active, deleted, registrationDate, timeZoneId, avatarId, null);
                            }
                            else
                            {
                                customer = CustomerManager.UpdateCustomer(customer.CustomerId, customer.CustomerGuid,
                                    email, username, passwordHash, saltKey, affiliateId, billingAddressId,
                                    shippingAddressId, lastPaymentMethodId, lastAppliedCouponCode,
                                    string.Empty, string.Empty, languageId, currencyId,
                                    (TaxDisplayTypeEnum)taxDisplayTypeId, isTaxExempt, isAdmin, isGuest,
                                    isForumModerator, totalForumPosts, signature, adminComment,
                                    active, deleted, registrationDate, timeZoneId, avatarId, customer.DateOfBirth);
                            }
                        }
                    }

                    //if (CustomerManager.FormFieldGenderEnabled)
                    //    customer.Gender = gender;
                    customer.Title =
                    customer.FirstName = firstName;
                    customer.LastName = lastName;
                    if (CustomerManager.FormFieldCompanyEnabled)
                        customer.Company = company;
                    if (CustomerManager.FormFieldStreetAddressEnabled)
                        customer.StreetAddress = streetAddress;
                    if (CustomerManager.FormFieldStreetAddress2Enabled)
                        customer.StreetAddress2 = streetAddress2;
                    if (CustomerManager.FormFieldPostCodeEnabled)
                        customer.ZipPostalCode = zipPostalCode;
                    if (CustomerManager.FormFieldCityEnabled)
                        customer.City = city;
                    if (CustomerManager.FormFieldPhoneEnabled)
                        customer.PhoneNumber = phoneNumber;
                    if (CustomerManager.FormFieldFaxEnabled)
                        customer.FaxNumber = faxNumber;
                    if (CustomerManager.FormFieldCountryEnabled)
                        customer.CountryId = countryId;
                    //if (CustomerManager.FormFieldStateEnabled)
                    //    customer.StateProvinceId = stateProvinceId;
                    if (CustomerManager.FormFieldNewsletterEnabled)
                        customer.ReceiveNewsletter = receiveNewsletter;

                    if (TaxManager.EUVatEnabled)
                    {
                        customer.VatNumber = vatNumber;
                        customer.VatNumberStatus = vatNumberStatus;
                    }
                }
            }
        }
Example #7
0
        /// <summary>
        /// Import products from XLS file
        /// </summary>
        /// <param name="filePath">Excel file path</param>
        public static void ImportProductsFromXls(string filePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(filePath))
            {
                excelHelper.Hdr = "YES";
                excelHelper.Imex = "1";

                DataTable dt = excelHelper.ReadTable("Products");
                foreach (DataRow dr in dt.Rows)
                {
                    string Name = dr["Name"].ToString();
                    string ShortDescription = dr["ShortDescription"].ToString();
                    string FullDescription = dr["FullDescription"].ToString();
                    int TemplateId = Convert.ToInt32(dr["TemplateId"]);
                    bool ShowOnHomePage = Convert.ToBoolean(dr["ShowOnHomePage"]);
                    string MetaKeywords = dr["MetaKeywords"].ToString();
                    string MetaDescription = dr["MetaDescription"].ToString();
                    string MetaTitle = dr["MetaTitle"].ToString();
                    bool AllowCustomerReviews = Convert.ToBoolean(dr["AllowCustomerReviews"]);
                    bool AllowCustomerRatings = Convert.ToBoolean(dr["AllowCustomerRatings"]);
                    bool Published = Convert.ToBoolean(dr["Published"]);
                    string SKU = dr["SKU"].ToString();
                    string ManufacturerPartNumber = dr["ManufacturerPartNumber"].ToString();
                    bool IsGiftCard = Convert.ToBoolean(dr["IsGiftCard"]);
                    int GiftCardType = Convert.ToInt32(dr["GiftCardType"]);
                    bool IsDownload = Convert.ToBoolean(dr["IsDownload"]);
                    int DownloadId = Convert.ToInt32(dr["DownloadId"]);
                    bool UnlimitedDownloads = Convert.ToBoolean(dr["UnlimitedDownloads"]);
                    int MaxNumberOfDownloads = Convert.ToInt32(dr["MaxNumberOfDownloads"]);
                    bool HasSampleDownload = Convert.ToBoolean(dr["HasSampleDownload"]);
                    int DownloadActivationType = Convert.ToInt32(dr["DownloadActivationType"]);
                    int SampleDownloadId = Convert.ToInt32(dr["SampleDownloadId"]);
                    bool HasUserAgreement = Convert.ToBoolean(dr["HasUserAgreement"]);
                    string UserAgreementText = dr["UserAgreementText"].ToString();
                    bool IsRecurring = Convert.ToBoolean(dr["IsRecurring"]);
                    int CycleLength = Convert.ToInt32(dr["CycleLength"]);
                    int CyclePeriod = Convert.ToInt32(dr["CyclePeriod"]);
                    int TotalCycles = Convert.ToInt32(dr["TotalCycles"]);
                    bool IsShipEnabled = Convert.ToBoolean(dr["IsShipEnabled"]);
                    bool IsFreeShipping = Convert.ToBoolean(dr["IsFreeShipping"]);
                    decimal AdditionalShippingCharge = Convert.ToDecimal(dr["AdditionalShippingCharge"]);
                    bool IsTaxExempt = Convert.ToBoolean(dr["IsTaxExempt"]);
                    int TaxCategoryId = Convert.ToInt32(dr["TaxCategoryId"]);
                    int ManageInventory = Convert.ToInt32(dr["ManageInventory"]);
                    int StockQuantity = Convert.ToInt32(dr["StockQuantity"]);
                    bool DisplayStockAvailability = Convert.ToBoolean(dr["DisplayStockAvailability"]);
                    bool DisplayStockQuantity = Convert.ToBoolean(dr["DisplayStockQuantity"]);
                    int MinStockQuantity = Convert.ToInt32(dr["MinStockQuantity"]);
                    int LowStockActivityId = Convert.ToInt32(dr["LowStockActivityId"]);
                    int NotifyAdminForQuantityBelow = Convert.ToInt32(dr["NotifyAdminForQuantityBelow"]);
                    int Backorders = Convert.ToInt32(dr["Backorders"]);
                    int OrderMinimumQuantity = Convert.ToInt32(dr["OrderMinimumQuantity"]);
                    int OrderMaximumQuantity = Convert.ToInt32(dr["OrderMaximumQuantity"]);
                    bool DisableBuyButton = Convert.ToBoolean(dr["DisableBuyButton"]);
                    bool CallForPrice = Convert.ToBoolean(dr["CallForPrice"]);
                    decimal Price = Convert.ToDecimal(dr["Price"]);
                    decimal OldPrice = Convert.ToDecimal(dr["OldPrice"]);
                    decimal ProductCost = Convert.ToDecimal(dr["ProductCost"]);
                    bool CustomerEntersPrice = Convert.ToBoolean(dr["CustomerEntersPrice"]);
                    decimal MinimumCustomerEnteredPrice = Convert.ToDecimal(dr["MinimumCustomerEnteredPrice"]);
                    decimal MaximumCustomerEnteredPrice = Convert.ToDecimal(dr["MaximumCustomerEnteredPrice"]);
                    decimal Weight = Convert.ToDecimal(dr["Weight"]);
                    decimal Length = Convert.ToDecimal(dr["Length"]);
                    decimal Width = Convert.ToDecimal(dr["Width"]);
                    decimal Height = Convert.ToDecimal(dr["Height"]);
                    DateTime CreatedOn = DateTime.FromOADate(Convert.ToDouble(dr["CreatedOn"]));

                    var productVariant = ProductManager.GetProductVariantBySKU(SKU);
                    if (productVariant != null)
                    {
                        var product = ProductManager.GetProductById(productVariant.ProductId);
                        product = ProductManager.UpdateProduct(product.ProductId, Name, ShortDescription,
                            FullDescription, product.AdminComment,
                            TemplateId, ShowOnHomePage, MetaKeywords, MetaDescription,
                            MetaTitle, product.SEName, AllowCustomerReviews, AllowCustomerRatings,
                            product.RatingSum, product.TotalRatingVotes,
                            Published, product.Deleted, CreatedOn, DateTime.UtcNow);

                        productVariant = ProductManager.UpdateProductVariant(productVariant.ProductVariantId,
                            productVariant.ProductId, productVariant.Name, SKU,
                            productVariant.Description, productVariant.AdminComment,
                            ManufacturerPartNumber, IsGiftCard, GiftCardType, IsDownload, DownloadId,
                            UnlimitedDownloads, MaxNumberOfDownloads, productVariant.DownloadExpirationDays,
                            (DownloadActivationTypeEnum)DownloadActivationType, HasSampleDownload,
                            SampleDownloadId, HasUserAgreement, UserAgreementText, IsRecurring,
                            CycleLength, CyclePeriod, TotalCycles, IsShipEnabled,
                            IsFreeShipping, AdditionalShippingCharge, IsTaxExempt,
                            TaxCategoryId, ManageInventory, StockQuantity,
                            DisplayStockAvailability, DisplayStockQuantity, MinStockQuantity,
                            (LowStockActivityEnum)LowStockActivityId, NotifyAdminForQuantityBelow,
                            Backorders, OrderMinimumQuantity,
                            OrderMaximumQuantity, productVariant.WarehouseId, DisableBuyButton,
                            CallForPrice, Price, OldPrice, ProductCost, CustomerEntersPrice,
                            MinimumCustomerEnteredPrice, MaximumCustomerEnteredPrice,
                            Weight, Length, Width, Height,
                            productVariant.PictureId, productVariant.AvailableStartDateTime,
                            productVariant.AvailableEndDateTime, productVariant.Published,
                            productVariant.Deleted, productVariant.DisplayOrder, CreatedOn, DateTime.UtcNow);
                    }
                    else
                    {
                        var product = ProductManager.InsertProduct(Name, ShortDescription, FullDescription,
                            string.Empty, TemplateId, ShowOnHomePage, MetaKeywords, MetaDescription,
                            MetaTitle, string.Empty, AllowCustomerReviews, AllowCustomerRatings, 0, 0,
                            Published, false, CreatedOn, DateTime.UtcNow);

                        productVariant = ProductManager.InsertProductVariant(product.ProductId,
                            string.Empty, SKU, string.Empty, string.Empty, ManufacturerPartNumber,
                            IsGiftCard, GiftCardType, IsDownload, DownloadId,
                            UnlimitedDownloads, MaxNumberOfDownloads, null, (DownloadActivationTypeEnum)DownloadActivationType,
                            HasSampleDownload, SampleDownloadId, HasUserAgreement, UserAgreementText, IsRecurring, CycleLength, CyclePeriod, TotalCycles,
                            IsShipEnabled, IsFreeShipping, AdditionalShippingCharge, IsTaxExempt,
                            TaxCategoryId, ManageInventory, StockQuantity,
                            DisplayStockAvailability, DisplayStockQuantity, MinStockQuantity,
                            (LowStockActivityEnum)LowStockActivityId, NotifyAdminForQuantityBelow,
                            Backorders, OrderMinimumQuantity,
                            OrderMaximumQuantity, 0, DisableBuyButton, CallForPrice,
                            Price, OldPrice, ProductCost, CustomerEntersPrice,
                            MinimumCustomerEnteredPrice, MaximumCustomerEnteredPrice,
                            Weight, Length, Width, Height, 0, null, null,
                            true, false, 1, CreatedOn, DateTime.UtcNow);
                    }
                }
            }
        }
        /// <summary>
        /// Import products from XLS file
        /// </summary>
        /// <param name="FilePath">Excel file path</param>
        public static void ImportProductsFromXLS(string FilePath)
        {
            using (ExcelHelper excelHelper = new ExcelHelper(FilePath))
            {
                excelHelper.HDR  = "YES";
                excelHelper.IMEX = "1";

                DataTable dt = excelHelper.ReadTable("Products");
                foreach (DataRow dr in dt.Rows)
                {
                    string   Name                 = dr["Name"].ToString();
                    string   ShortDescription     = dr["ShortDescription"].ToString();
                    string   FullDescription      = dr["FullDescription"].ToString();
                    int      ProductTypeID        = Convert.ToInt32(dr["ProductTypeID"]);
                    int      TemplateID           = Convert.ToInt32(dr["TemplateID"]);
                    bool     ShowOnHomePage       = Convert.ToBoolean(dr["ShowOnHomePage"]);
                    string   MetaKeywords         = dr["MetaKeywords"].ToString();
                    string   MetaDescription      = dr["MetaDescription"].ToString();
                    string   MetaTitle            = dr["MetaTitle"].ToString();
                    bool     AllowCustomerReviews = Convert.ToBoolean(dr["AllowCustomerReviews"]);
                    bool     AllowCustomerRatings = Convert.ToBoolean(dr["AllowCustomerRatings"]);
                    bool     Published            = Convert.ToBoolean(dr["Published"]);
                    string   SKU = dr["SKU"].ToString();
                    string   ManufacturerPartNumber      = dr["ManufacturerPartNumber"].ToString();
                    bool     IsDownload                  = Convert.ToBoolean(dr["IsDownload"]);
                    int      DownloadID                  = Convert.ToInt32(dr["DownloadID"]);
                    bool     UnlimitedDownloads          = Convert.ToBoolean(dr["UnlimitedDownloads"]);
                    int      MaxNumberOfDownloads        = Convert.ToInt32(dr["MaxNumberOfDownloads"]);
                    bool     HasSampleDownload           = Convert.ToBoolean(dr["HasSampleDownload"]);
                    int      SampleDownloadID            = Convert.ToInt32(dr["SampleDownloadID"]);
                    bool     IsShipEnabled               = Convert.ToBoolean(dr["IsShipEnabled"]);
                    bool     IsFreeShipping              = Convert.ToBoolean(dr["IsFreeShipping"]);
                    decimal  AdditionalShippingCharge    = Convert.ToDecimal(dr["AdditionalShippingCharge"]);
                    bool     IsTaxExempt                 = Convert.ToBoolean(dr["IsTaxExempt"]);
                    int      TaxCategoryID               = Convert.ToInt32(dr["TaxCategoryID"]);
                    bool     ManageInventory             = Convert.ToBoolean(dr["ManageInventory"]);
                    int      StockQuantity               = Convert.ToInt32(dr["StockQuantity"]);
                    int      MinStockQuantity            = Convert.ToInt32(dr["MinStockQuantity"]);
                    int      LowStockActivityID          = Convert.ToInt32(dr["LowStockActivityID"]);
                    int      NotifyAdminForQuantityBelow = Convert.ToInt32(dr["NotifyAdminForQuantityBelow"]);
                    int      OrderMinimumQuantity        = Convert.ToInt32(dr["OrderMinimumQuantity"]);
                    int      OrderMaximumQuantity        = Convert.ToInt32(dr["OrderMaximumQuantity"]);
                    bool     DisableBuyButton            = Convert.ToBoolean(dr["DisableBuyButton"]);
                    decimal  Price     = Convert.ToDecimal(dr["Price"]);
                    decimal  OldPrice  = Convert.ToDecimal(dr["OldPrice"]);
                    decimal  Weight    = Convert.ToDecimal(dr["Weight"]);
                    decimal  Length    = Convert.ToDecimal(dr["Length"]);
                    decimal  Width     = Convert.ToDecimal(dr["Width"]);
                    decimal  Height    = Convert.ToDecimal(dr["Height"]);
                    DateTime CreatedOn = Convert.ToDateTime(dr["CreatedOn"]);

                    Product product = ProductManager.InsertProduct(Name, ShortDescription, FullDescription,
                                                                   string.Empty, ProductTypeID, TemplateID, ShowOnHomePage, MetaKeywords, MetaDescription,
                                                                   MetaTitle, string.Empty, AllowCustomerReviews, AllowCustomerRatings, 0, 0,
                                                                   Published, false, CreatedOn, DateTime.Now);

                    ProductVariant productVariant = ProductManager.InsertProductVariant(product.ProductID,
                                                                                        string.Empty, SKU, string.Empty, string.Empty, ManufacturerPartNumber, IsDownload, DownloadID,
                                                                                        UnlimitedDownloads, MaxNumberOfDownloads, HasSampleDownload, SampleDownloadID,
                                                                                        IsShipEnabled, IsFreeShipping, AdditionalShippingCharge, IsTaxExempt,
                                                                                        TaxCategoryID, ManageInventory, StockQuantity, MinStockQuantity,
                                                                                        (LowStockActivityEnum)LowStockActivityID, NotifyAdminForQuantityBelow,
                                                                                        OrderMinimumQuantity, OrderMaximumQuantity, 0, DisableBuyButton, Price, OldPrice, Weight, Length, Width, Height, 0, null, null,
                                                                                        true, false, 1, CreatedOn, DateTime.Now);
                }
            }
        }