/// <summary>
/// Export charge to XLS
/// </summary>
/// <param name="filePath">File path to use</param>
/// <param name="orders">Orders</param>
public void ExportChargeToXls(string filePath, List<Charge> charges)
{
using (ExcelHelper excelHelper = new ExcelHelper(filePath))
{
excelHelper.Hdr = "YES";
excelHelper.Imex = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
tableDefinition.Add("ChargeID", "int");
tableDefinition.Add("Amount", "decimal");
tableDefinition.Add("IsInvoiceCharge", "bit");
excelHelper.WriteTable("Charge", tableDefinition);
foreach (var charge in charges)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Charge] (ChargeID, Amount, IsInvoiceCharge) VALUES (");
sb.Append(charge.ChargeID); sb.Append(",");
sb.Append('"'); sb.Append(charge.Amount); sb.Append('"'); sb.Append(",");
sb.Append(charge.IsInvoiceCharge);
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
/// <summary>
/// Export customer list to XLS
/// </summary>
/// <param name="FilePath">File path to use</param>
/// <param name="customers">Customers</param>
public static void ExportCustomersToXLS(string FilePath, CustomerCollection customers)
{
using (ExcelHelper excelHelper = new ExcelHelper(FilePath))
{
excelHelper.HDR = "YES";
excelHelper.IMEX = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string,string>();
tableDefinition.Add("CustomerID", "int");
tableDefinition.Add("CustomerGUID", "uniqueidentifier");
tableDefinition.Add("Email", "nvarchar(255)");
tableDefinition.Add("Username", "nvarchar(255)");
tableDefinition.Add("PasswordHash", "nvarchar(255)");
tableDefinition.Add("SaltKey", "nvarchar(255)");
tableDefinition.Add("AffiliateID", "int");
tableDefinition.Add("BillingAddressID", "int");
tableDefinition.Add("ShippingAddressID", "int");
tableDefinition.Add("LastPaymentMethodID", "int");
tableDefinition.Add("LastAppliedCouponCode", "nvarchar(255)");
tableDefinition.Add("LanguageID", "int");
tableDefinition.Add("CurrencyID", "int");
tableDefinition.Add("TaxDisplayTypeID", "int");
tableDefinition.Add("IsTaxExempt", "nvarchar(5)");
tableDefinition.Add("IsAdmin", "nvarchar(5)");
tableDefinition.Add("IsGuest", "nvarchar(5)");
tableDefinition.Add("IsForumModerator", "nvarchar(5)");
tableDefinition.Add("TotalForumPosts", "int");
tableDefinition.Add("Signature", "nvarchar(255)");
tableDefinition.Add("AdminComment", "nvarchar(255)");
tableDefinition.Add("Active", "nvarchar(5)");
tableDefinition.Add("Deleted", "nvarchar(5)");
tableDefinition.Add("RegistrationDate", "datetime");
tableDefinition.Add("TimeZoneID", "nvarchar(200)");
tableDefinition.Add("AvatarID", "int");
tableDefinition.Add("Gender", "nvarchar(100)");
tableDefinition.Add("FirstName", "nvarchar(100)");
tableDefinition.Add("LastName", "nvarchar(100)");
tableDefinition.Add("Company", "nvarchar(100)");
tableDefinition.Add("StreetAddress", "nvarchar(100)");
tableDefinition.Add("StreetAddress2", "nvarchar(100)");
tableDefinition.Add("ZipPostalCode", "nvarchar(100)");
tableDefinition.Add("City", "nvarchar(100)");
tableDefinition.Add("PhoneNumber", "nvarchar(100)");
tableDefinition.Add("FaxNumber", "nvarchar(100)");
tableDefinition.Add("CountryID", "int");
tableDefinition.Add("StateProvinceID", "int");
tableDefinition.Add("ReceiveNewsletter", "nvarchar(5)");
excelHelper.WriteTable("Customers", tableDefinition);
foreach (Customer customer in customers)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Customers] (CustomerID, CustomerGUID, Email, Username, PasswordHash, SaltKey, AffiliateID, BillingAddressID, ShippingAddressID, LastPaymentMethodID, LastAppliedCouponCode, LanguageID, CurrencyID, TaxDisplayTypeID, IsTaxExempt, IsAdmin, IsGuest, IsForumModerator, TotalForumPosts, Signature, AdminComment, Active, Deleted, RegistrationDate, TimeZoneID, AvatarID, Gender, FirstName, LastName, Company, StreetAddress, StreetAddress2, ZipPostalCode, City, PhoneNumber, FaxNumber, CountryID, StateProvinceID, ReceiveNewsletter) VALUES (");
sb.Append(customer.CustomerID); sb.Append(",");
sb.Append('"'); sb.Append(customer.CustomerGUID); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Email.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Username); sb.Append("\",");
sb.Append('"'); sb.Append(customer.PasswordHash.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.SaltKey.Replace('"', '\'')); sb.Append("\",");
sb.Append(customer.AffiliateID); sb.Append(",");
sb.Append(customer.BillingAddressID); sb.Append(",");
sb.Append(customer.ShippingAddressID); sb.Append(",");
sb.Append(customer.LastPaymentMethodID); sb.Append(",");
sb.Append('"'); sb.Append(customer.LastAppliedCouponCode.Replace('"', '\'')); sb.Append("\",");
sb.Append(customer.LanguageID); sb.Append(",");
sb.Append(customer.CurrencyID); sb.Append(",");
sb.Append(customer.TaxDisplayTypeID); sb.Append(',');
sb.Append('"'); sb.Append(customer.IsTaxExempt); sb.Append("\",");
sb.Append('"'); sb.Append(customer.IsAdmin); sb.Append("\",");
sb.Append('"'); sb.Append(customer.IsGuest); sb.Append("\",");
sb.Append('"'); sb.Append(customer.IsForumModerator); sb.Append("\",");
sb.Append(customer.TotalForumPosts); sb.Append(',');
sb.Append('"'); sb.Append(customer.Signature.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.AdminComment.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Active); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Deleted); sb.Append("\",");
sb.Append('"'); sb.Append(customer.RegistrationDate); sb.Append("\",");
sb.Append('"'); sb.Append(customer.TimeZoneID); sb.Append("\",");
sb.Append(customer.AvatarID); sb.Append(',');
//custom properties
sb.Append('"'); sb.Append(customer.Gender); sb.Append("\",");
sb.Append('"'); sb.Append(customer.FirstName); sb.Append("\",");
sb.Append('"'); sb.Append(customer.LastName); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Company); sb.Append("\",");
sb.Append('"'); sb.Append(customer.StreetAddress); sb.Append("\",");
sb.Append('"'); sb.Append(customer.StreetAddress2); sb.Append("\",");
sb.Append('"'); sb.Append(customer.ZipPostalCode); sb.Append("\",");
sb.Append('"'); sb.Append(customer.City); sb.Append("\",");
sb.Append('"'); sb.Append(customer.PhoneNumber); sb.Append("\",");
sb.Append('"'); sb.Append(customer.FaxNumber); sb.Append("\",");
sb.Append(customer.CountryID); sb.Append(',');
sb.Append(customer.StateProvinceID); sb.Append(',');
sb.Append('"'); sb.Append(customer.ReceiveNewsletter); sb.Append("\"");
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
/// <summary>
/// Export customer list to XLS
/// </summary>
/// <param name="filePath">File path to use</param>
/// <param name="customers">Customers</param>
public void ExportCustomersToXls(string filePath, List<Customer> customers)
{
using (ExcelHelper excelHelper = new ExcelHelper(filePath))
{
excelHelper.Hdr = "YES";
excelHelper.Imex = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string,string>();
tableDefinition.Add("CustomerId", "int");
tableDefinition.Add("CustomerGuid", "uniqueidentifier");
tableDefinition.Add("Email", "nvarchar(255)");
tableDefinition.Add("Username", "nvarchar(255)");
tableDefinition.Add("PasswordHash", "nvarchar(255)");
tableDefinition.Add("SaltKey", "nvarchar(255)");
tableDefinition.Add("AffiliateId", "int");
tableDefinition.Add("LanguageId", "int");
tableDefinition.Add("CurrencyId", "int");
tableDefinition.Add("TaxDisplayTypeId", "int");
tableDefinition.Add("IsTaxExempt", "nvarchar(5)");
tableDefinition.Add("IsAdmin", "nvarchar(5)");
tableDefinition.Add("IsGuest", "nvarchar(5)");
tableDefinition.Add("IsForumModerator", "nvarchar(5)");
tableDefinition.Add("TotalForumPosts", "int");
tableDefinition.Add("Signature", "nvarchar(255)");
tableDefinition.Add("AdminComment", "nvarchar(255)");
tableDefinition.Add("Active", "nvarchar(5)");
tableDefinition.Add("Deleted", "nvarchar(5)");
tableDefinition.Add("RegistrationDate", "decimal");
tableDefinition.Add("TimeZoneId", "nvarchar(200)");
tableDefinition.Add("AvatarId", "int");
tableDefinition.Add("Gender", "nvarchar(100)");
tableDefinition.Add("FirstName", "nvarchar(100)");
tableDefinition.Add("LastName", "nvarchar(100)");
tableDefinition.Add("Company", "nvarchar(100)");
tableDefinition.Add("VatNumber", "nvarchar(100)");
tableDefinition.Add("VatNumberStatus", "int");
tableDefinition.Add("StreetAddress", "nvarchar(100)");
tableDefinition.Add("StreetAddress2", "nvarchar(100)");
tableDefinition.Add("ZipPostalCode", "nvarchar(100)");
tableDefinition.Add("City", "nvarchar(100)");
tableDefinition.Add("PhoneNumber", "nvarchar(100)");
tableDefinition.Add("FaxNumber", "nvarchar(100)");
tableDefinition.Add("CountryId", "int");
tableDefinition.Add("StateProvinceId", "int");
tableDefinition.Add("ReceiveNewsletter", "nvarchar(5)");
excelHelper.WriteTable("Customers", tableDefinition);
string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty);
foreach (var customer in customers)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Customers] (CustomerId, CustomerGuid, Email, Username, PasswordHash, SaltKey, AffiliateId, LanguageId, CurrencyId, TaxDisplayTypeId, IsTaxExempt, IsAdmin, IsGuest, IsForumModerator, TotalForumPosts, Signature, AdminComment, Active, Deleted, RegistrationDate, TimeZoneId, AvatarId, Gender, FirstName, LastName, Company, VatNumber, VatNumberStatus, StreetAddress, StreetAddress2, ZipPostalCode, City, PhoneNumber, FaxNumber, CountryId, StateProvinceId, ReceiveNewsletter) VALUES (");
sb.Append(customer.CustomerId); sb.Append(",");
sb.Append('"'); sb.Append(customer.CustomerGuid); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Email.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Username); sb.Append("\",");
sb.Append('"'); sb.Append(customer.PasswordHash.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.SaltKey.Replace('"', '\'')); sb.Append("\",");
sb.Append(customer.AffiliateId); sb.Append(",");
sb.Append(customer.LanguageId); sb.Append(",");
sb.Append(customer.CurrencyId); sb.Append(",");
sb.Append(customer.TaxDisplayTypeId); sb.Append(',');
sb.Append('"'); sb.Append(customer.IsTaxExempt); sb.Append("\",");
sb.Append('"'); sb.Append(customer.IsAdmin); sb.Append("\",");
sb.Append('"'); sb.Append(customer.IsGuest); sb.Append("\",");
sb.Append('"'); sb.Append(customer.IsForumModerator); sb.Append("\",");
sb.Append(customer.TotalForumPosts); sb.Append(',');
sb.Append('"'); sb.Append(customer.Signature.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.AdminComment.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Active); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Deleted); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(customer.RegistrationDate.ToOADate()); sb.Append(decimalQuoter); sb.Append(",");
sb.Append('"'); sb.Append(customer.TimeZoneId); sb.Append("\",");
sb.Append(customer.AvatarId); sb.Append(',');
//custom properties
sb.Append('"'); sb.Append(customer.Gender); sb.Append("\",");
sb.Append('"'); sb.Append(customer.FirstName); sb.Append("\",");
sb.Append('"'); sb.Append(customer.LastName); sb.Append("\",");
sb.Append('"'); sb.Append(customer.Company); sb.Append("\",");
sb.Append('"'); sb.Append(customer.VatNumber); sb.Append("\",");
sb.Append(((int)customer.VatNumberStatus).ToString()); sb.Append(',');
sb.Append('"'); sb.Append(customer.StreetAddress); sb.Append("\",");
sb.Append('"'); sb.Append(customer.StreetAddress2); sb.Append("\",");
sb.Append('"'); sb.Append(customer.ZipPostalCode); sb.Append("\",");
sb.Append('"'); sb.Append(customer.City); sb.Append("\",");
sb.Append('"'); sb.Append(customer.PhoneNumber); sb.Append("\",");
sb.Append('"'); sb.Append(customer.FaxNumber); sb.Append("\",");
sb.Append(customer.CountryId); sb.Append(',');
sb.Append(customer.StateProvinceId); sb.Append(',');
sb.Append('"'); sb.Append(customer.ReceiveNewsletter); sb.Append("\"");
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
/// <summary>
/// Export products to XLS
/// </summary>
/// <param name="filePath">File path to use</param>
/// <param name="products">Products</param>
public void ExportProductsToXls(string filePath, List<Product> products)
{
using (ExcelHelper excelHelper = new ExcelHelper(filePath))
{
excelHelper.Hdr = "YES";
excelHelper.Imex = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
tableDefinition.Add("Name", "ntext");
tableDefinition.Add("ShortDescription", "ntext");
tableDefinition.Add("FullDescription", "ntext");
tableDefinition.Add("TemplateId", "int");
tableDefinition.Add("ShowOnHomePage", "nvarchar(5)");
tableDefinition.Add("MetaKeywords", "ntext");
tableDefinition.Add("MetaDescription", "ntext");
tableDefinition.Add("MetaTitle", "ntext");
tableDefinition.Add("AllowCustomerReviews", "nvarchar(5)");
tableDefinition.Add("AllowCustomerRatings", "nvarchar(5)");
tableDefinition.Add("Published", "nvarchar(5)");
tableDefinition.Add("SKU", "ntext");
tableDefinition.Add("ManufacturerPartNumber", "ntext");
tableDefinition.Add("IsGiftCard", "nvarchar(5)");
tableDefinition.Add("GiftCardType", "int");
tableDefinition.Add("IsDownload", "nvarchar(5)");
tableDefinition.Add("DownloadId", "int");
tableDefinition.Add("UnlimitedDownloads", "nvarchar(5)");
tableDefinition.Add("MaxNumberOfDownloads", "int");
tableDefinition.Add("DownloadActivationType", "int");
tableDefinition.Add("HasSampleDownload", "nvarchar(5)");
tableDefinition.Add("SampleDownloadId", "int");
tableDefinition.Add("HasUserAgreement", "nvarchar(5)");
tableDefinition.Add("UserAgreementText", "ntext");
tableDefinition.Add("IsRecurring", "nvarchar(5)");
tableDefinition.Add("CycleLength", "int");
tableDefinition.Add("CyclePeriod", "int");
tableDefinition.Add("TotalCycles", "int");
tableDefinition.Add("IsShipEnabled", "nvarchar(5)");
tableDefinition.Add("IsFreeShipping", "nvarchar(5)");
tableDefinition.Add("AdditionalShippingCharge", "decimal");
tableDefinition.Add("IsTaxExempt", "nvarchar(5)");
tableDefinition.Add("TaxCategoryId", "int");
tableDefinition.Add("ManageInventory", "int");
tableDefinition.Add("StockQuantity", "int");
tableDefinition.Add("DisplayStockAvailability", "nvarchar(5)");
tableDefinition.Add("DisplayStockQuantity", "nvarchar(5)");
tableDefinition.Add("MinStockQuantity", "int");
tableDefinition.Add("LowStockActivityId", "int");
tableDefinition.Add("NotifyAdminForQuantityBelow", "int");
tableDefinition.Add("Backorders", "int");
tableDefinition.Add("OrderMinimumQuantity", "int");
tableDefinition.Add("OrderMaximumQuantity", "int");
tableDefinition.Add("DisableBuyButton", "nvarchar(5)");
tableDefinition.Add("CallForPrice", "nvarchar(5)");
tableDefinition.Add("Price", "decimal");
tableDefinition.Add("OldPrice", "decimal");
tableDefinition.Add("ProductCost", "decimal");
tableDefinition.Add("CustomerEntersPrice", "nvarchar(5)");
tableDefinition.Add("MinimumCustomerEnteredPrice", "decimal");
tableDefinition.Add("MaximumCustomerEnteredPrice", "decimal");
tableDefinition.Add("Weight", "decimal");
tableDefinition.Add("Length", "decimal");
tableDefinition.Add("Width", "decimal");
tableDefinition.Add("Height", "decimal");
tableDefinition.Add("CreatedOn", "decimal");
excelHelper.WriteTable("Products", tableDefinition);
string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty);
foreach (var p in products)
{
var productVariants = IoC.Resolve<IProductService>().GetProductVariantsByProductId(p.ProductId, true);
foreach (var pv in productVariants)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Products] (Name, ShortDescription,FullDescription,TemplateId,ShowOnHomePage,MetaKeywords,MetaDescription,MetaTitle,AllowCustomerReviews,AllowCustomerRatings,Published,SKU,ManufacturerPartNumber,IsGiftCard,GiftCardType,IsDownload,DownloadId,UnlimitedDownloads,MaxNumberOfDownloads,DownloadActivationType,HasSampleDownload,SampleDownloadId,HasUserAgreement,UserAgreementText,IsRecurring,CycleLength,CyclePeriod,TotalCycles,IsShipEnabled,IsFreeShipping,AdditionalShippingCharge,IsTaxExempt,TaxCategoryId,ManageInventory,StockQuantity,DisplayStockAvailability,DisplayStockQuantity,MinStockQuantity,LowStockActivityId,NotifyAdminForQuantityBelow,Backorders,OrderMinimumQuantity,OrderMaximumQuantity,DisableBuyButton,CallForPrice,Price,OldPrice,ProductCost,CustomerEntersPrice,MinimumCustomerEnteredPrice,MaximumCustomerEnteredPrice,Weight, Length, Width, Height, CreatedOn) VALUES (");
sb.Append('"'); sb.Append(p.Name.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(p.ShortDescription.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(p.FullDescription.Replace('"', '\'')); sb.Append("\",");
sb.Append(p.TemplateId); sb.Append(",");
sb.Append('"'); sb.Append(p.ShowOnHomePage); sb.Append("\",");
sb.Append('"'); sb.Append(p.MetaKeywords.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(p.MetaDescription.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(p.MetaTitle.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(p.AllowCustomerReviews); sb.Append("\",");
sb.Append('"'); sb.Append(p.AllowCustomerRatings); sb.Append("\",");
sb.Append('"'); sb.Append(p.Published); sb.Append("\",");
sb.Append('"'); sb.Append(pv.SKU.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(pv.ManufacturerPartNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(pv.IsGiftCard); sb.Append("\",");
sb.Append('"'); sb.Append(pv.GiftCardType); sb.Append("\",");
sb.Append('"'); sb.Append(pv.IsDownload); sb.Append("\",");
sb.Append(pv.DownloadId); sb.Append(",");
sb.Append('"'); sb.Append(pv.UnlimitedDownloads); sb.Append("\",");
sb.Append(pv.MaxNumberOfDownloads); sb.Append(",");
sb.Append(pv.DownloadActivationType); sb.Append(",");
sb.Append('"'); sb.Append(pv.HasSampleDownload); sb.Append("\",");
sb.Append(pv.SampleDownloadId); sb.Append(",");
sb.Append('"'); sb.Append(pv.HasUserAgreement); sb.Append("\",");
sb.Append('"'); sb.Append(pv.UserAgreementText.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(pv.IsRecurring); sb.Append("\",");
sb.Append(pv.CycleLength); sb.Append(",");
sb.Append(pv.CyclePeriod); sb.Append(",");
sb.Append(pv.TotalCycles); sb.Append(",");
sb.Append('"'); sb.Append(pv.IsShipEnabled); sb.Append("\",");
sb.Append('"'); sb.Append(pv.IsFreeShipping); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(pv.AdditionalShippingCharge); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append('"'); sb.Append(pv.IsTaxExempt); sb.Append("\",");
sb.Append(pv.TaxCategoryId); sb.Append(",");
sb.Append(pv.ManageInventory); sb.Append(",");
sb.Append(pv.StockQuantity); sb.Append(",");
sb.Append('"'); sb.Append(pv.DisplayStockAvailability); sb.Append("\",");
sb.Append('"'); sb.Append(pv.DisplayStockQuantity); sb.Append("\",");
sb.Append(pv.MinStockQuantity); sb.Append(",");
sb.Append(pv.LowStockActivityId); sb.Append(",");
sb.Append(pv.NotifyAdminForQuantityBelow); sb.Append(",");
sb.Append(pv.Backorders); sb.Append(",");
sb.Append(pv.OrderMinimumQuantity); sb.Append(",");
sb.Append(pv.OrderMaximumQuantity); sb.Append(",");
sb.Append('"'); sb.Append(pv.DisableBuyButton); sb.Append("\",");
sb.Append('"'); sb.Append(pv.CallForPrice); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(pv.Price); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.OldPrice); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.ProductCost); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append('"'); sb.Append(pv.CustomerEntersPrice); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(pv.MinimumCustomerEnteredPrice); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.MaximumCustomerEnteredPrice); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.Weight); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.Length); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.Width); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.Height); sb.Append(decimalQuoter); sb.Append(',');//decimal
sb.Append(decimalQuoter); sb.Append(pv.CreatedOn.ToOADate()); sb.Append(decimalQuoter);
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
}
/// <summary>
/// Export orders to XLS
/// </summary>
/// <param name="filePath">File path to use</param>
/// <param name="orders">Orders</param>
public void ExportOrdersToXls(string filePath, List<Order> orders)
{
using (ExcelHelper excelHelper = new ExcelHelper(filePath))
{
excelHelper.Hdr = "YES";
excelHelper.Imex = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
tableDefinition.Add("OrderId", "int");
tableDefinition.Add("OrderGuid", "uniqueidentifier");
tableDefinition.Add("CustomerId", "int");
tableDefinition.Add("OrderSubtotalInclTax", "decimal");
tableDefinition.Add("OrderSubtotalExclTax", "decimal");
tableDefinition.Add("OrderSubTotalDiscountInclTax", "decimal");
tableDefinition.Add("OrderSubTotalDiscountExclTax", "decimal");
tableDefinition.Add("OrderShippingInclTax", "decimal");
tableDefinition.Add("OrderShippingExclTax", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeInclTax", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeExclTax", "decimal");
tableDefinition.Add("TaxRates", "nvarchar(255)");
tableDefinition.Add("OrderTax", "decimal");
tableDefinition.Add("OrderTotal", "decimal");
tableDefinition.Add("RefundedAmount", "decimal");
tableDefinition.Add("OrderDiscount", "decimal");
tableDefinition.Add("OrderSubtotalInclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderSubtotalExclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderSubTotalDiscountInclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderSubTotalDiscountExclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderShippingInclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderShippingExclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeInclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeExclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("TaxRatesInCustomerCurrency", "nvarchar(255)");
tableDefinition.Add("OrderTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderTotalInCustomerCurrency", "decimal");
tableDefinition.Add("OrderDiscountInCustomerCurrency", "decimal");
tableDefinition.Add("CustomerCurrencyCode", "nvarchar(5)");
tableDefinition.Add("OrderWeight", "decimal");
tableDefinition.Add("AffiliateId", "int");
tableDefinition.Add("OrderStatusId", "int");
tableDefinition.Add("PaymentMethodId", "int");
tableDefinition.Add("PaymentMethodName", "nvarchar(100)");
tableDefinition.Add("PurchaseOrderNumber", "nvarchar(100)");
tableDefinition.Add("PaymentStatusId", "int");
tableDefinition.Add("BillingFirstName", "nvarchar(100)");
tableDefinition.Add("BillingLastName", "nvarchar(100)");
tableDefinition.Add("BillingPhoneNumber", "nvarchar(50)");
tableDefinition.Add("BillingEmail", "nvarchar(255)");
tableDefinition.Add("BillingFaxNumber", "nvarchar(50)");
tableDefinition.Add("BillingCompany", "nvarchar(100)");
tableDefinition.Add("BillingAddress1", "nvarchar(100)");
tableDefinition.Add("BillingAddress2", "nvarchar(100)");
tableDefinition.Add("BillingCity", "nvarchar(100)");
tableDefinition.Add("BillingStateProvince", "nvarchar(100)");
tableDefinition.Add("BillingZipPostalCode", "nvarchar(100)");
tableDefinition.Add("BillingCountry", "nvarchar(100)");
tableDefinition.Add("ShippingStatusId", "int");
tableDefinition.Add("ShippingFirstName", "nvarchar(100)");
tableDefinition.Add("ShippingLastName", "nvarchar(100)");
tableDefinition.Add("ShippingPhoneNumber", "nvarchar(50)");
tableDefinition.Add("ShippingEmail", "nvarchar(255)");
tableDefinition.Add("ShippingFaxNumber", "nvarchar(50)");
tableDefinition.Add("ShippingCompany", "nvarchar(100)");
tableDefinition.Add("ShippingAddress1", "nvarchar(100)");
tableDefinition.Add("ShippingAddress2", "nvarchar(100)");
tableDefinition.Add("ShippingCity", "nvarchar(100)");
tableDefinition.Add("ShippingStateProvince", "nvarchar(100)");
tableDefinition.Add("ShippingZipPostalCode", "nvarchar(100)");
tableDefinition.Add("ShippingCountry", "nvarchar(100)");
tableDefinition.Add("ShippingMethod", "nvarchar(100)");
tableDefinition.Add("ShippingRateComputationMethodId", "int");
tableDefinition.Add("VatNumber", "nvarchar(100)");
tableDefinition.Add("CreatedOn", "decimal");
excelHelper.WriteTable("Orders", tableDefinition);
string decimalQuoter = (CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator.Equals(",") ? "\"" : String.Empty);
foreach (var order in orders)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Orders] (OrderId, OrderGuid, CustomerId, OrderSubtotalInclTax, OrderSubtotalExclTax, OrderSubTotalDiscountInclTax, OrderSubTotalDiscountExclTax, OrderShippingInclTax, OrderShippingExclTax, PaymentMethodAdditionalFeeInclTax, PaymentMethodAdditionalFeeExclTax, TaxRates, OrderTax, OrderTotal, RefundedAmount, OrderDiscount, OrderSubtotalInclTaxInCustomerCurrency, OrderSubtotalExclTaxInCustomerCurrency, OrderSubTotalDiscountInclTaxInCustomerCurrency, OrderSubTotalDiscountExclTaxInCustomerCurrency, OrderShippingInclTaxInCustomerCurrency, OrderShippingExclTaxInCustomerCurrency, PaymentMethodAdditionalFeeInclTaxInCustomerCurrency, PaymentMethodAdditionalFeeExclTaxInCustomerCurrency, TaxRatesInCustomerCurrency, OrderTaxInCustomerCurrency, OrderTotalInCustomerCurrency, OrderDiscountInCustomerCurrency, CustomerCurrencyCode, OrderWeight, AffiliateId, OrderStatusId, PaymentMethodId, PaymentMethodName, PurchaseOrderNumber, PaymentStatusId, BillingFirstName, BillingLastName, BillingPhoneNumber, BillingEmail, BillingFaxNumber, BillingCompany, BillingAddress1, BillingAddress2, BillingCity, BillingStateProvince, BillingZipPostalCode, BillingCountry, ShippingStatusId, ShippingFirstName, ShippingLastName, ShippingPhoneNumber, ShippingEmail, ShippingFaxNumber, ShippingCompany, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingStateProvince, ShippingZipPostalCode, ShippingCountry, ShippingMethod, ShippingRateComputationMethodId, VatNumber, CreatedOn) VALUES (");
sb.Append(order.OrderId); sb.Append(",");
sb.Append('"'); sb.Append(order.OrderGuid); sb.Append("\",");
sb.Append(order.CustomerId); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalInclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalExclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountInclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountExclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderShippingInclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderShippingExclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeInclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeExclTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append('"'); sb.Append(order.TaxRates.Replace('"', '\'')); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(order.OrderTax); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderTotal); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.RefundedAmount); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderDiscount); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubtotalExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderSubTotalDiscountExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderShippingInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderShippingExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeInclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.PaymentMethodAdditionalFeeExclTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append('"'); sb.Append(order.TaxRatesInCustomerCurrency.Replace('"', '\'')); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(order.OrderTaxInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderTotalInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(decimalQuoter); sb.Append(order.OrderDiscountInCustomerCurrency); sb.Append(decimalQuoter); sb.Append(",");
sb.Append('"'); sb.Append(order.CustomerCurrencyCode.Replace('"', '\'')); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(order.OrderWeight); sb.Append(decimalQuoter); sb.Append(",");
sb.Append(order.AffiliateId); sb.Append(",");
sb.Append(order.OrderStatusId); sb.Append(",");
sb.Append(order.PaymentMethodId); sb.Append(",");
sb.Append('"'); sb.Append(order.PaymentMethodName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.PurchaseOrderNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append(order.PaymentStatusId); sb.Append(",");
sb.Append('"'); sb.Append(order.BillingFirstName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingLastName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingPhoneNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingEmail.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingFaxNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingCompany.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingAddress1.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingAddress2.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingCity.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingStateProvince.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingZipPostalCode.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingCountry.Replace('"', '\'')); sb.Append("\",");
sb.Append(order.ShippingStatusId); sb.Append(",");
sb.Append('"'); sb.Append(order.ShippingFirstName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingLastName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingPhoneNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingEmail.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingFaxNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingCompany.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingAddress1.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingAddress2.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingCity.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingStateProvince.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingZipPostalCode.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingCountry.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingMethod.Replace('"', '\'')); sb.Append("\",");
sb.Append(order.ShippingRateComputationMethodId); sb.Append(",");
sb.Append('"'); sb.Append(order.VatNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append(decimalQuoter); sb.Append(order.CreatedOn.ToOADate()); sb.Append(decimalQuoter);
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
public void ExportInvoiceToXls(string filePath, List<Invoice> invoices)
{
using (ExcelHelper excelHelper = new ExcelHelper(filePath))
{
excelHelper.Hdr = "YES";
excelHelper.Imex = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
tableDefinition.Add("InvoiceID", "int");
tableDefinition.Add("Vendor", "string");
tableDefinition.Add("Amount", "decimal");
tableDefinition.Add("InvoiceDate", "DateTime");
excelHelper.WriteTable("Invoice", tableDefinition);
foreach (var invoice in invoices)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Invoice] (InvoiceID, Vendor, Amount,InvoiceDate) VALUES (");
sb.Append(invoice.InvoiceID); sb.Append(",");
sb.Append('"'); sb.Append(invoice.Vendor.CompanyName); sb.Append('"'); sb.Append(",");
sb.Append('"'); sb.Append(invoice.Amount); sb.Append('"'); sb.Append(",");
sb.Append('"'); sb.Append(invoice.CreatedOn); sb.Append('"');
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
/// <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);
}
}
}
/// <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);
}
}
}
/// <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;
}
}
}
}
/// <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 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 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 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.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;
}
}
}
}
/// <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>
/// Export products to XLS
/// </summary>
/// <param name="FilePath">File path to use</param>
/// <param name="products">Products</param>
public static void ExportProductsToXLS(string FilePath, List<Product> products)
{
using (ExcelHelper excelHelper = new ExcelHelper(FilePath))
{
excelHelper.HDR = "YES";
excelHelper.IMEX = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
int maxStringLength = 200;
tableDefinition.Add("Name", string.Format("nvarchar({0})",maxStringLength));
tableDefinition.Add("ShortDescription", string.Format("nvarchar({0})", maxStringLength));
tableDefinition.Add("FullDescription", string.Format("nvarchar({0})", maxStringLength));
tableDefinition.Add("ProductTypeID", "int");
tableDefinition.Add("TemplateID", "int");
tableDefinition.Add("ShowOnHomePage", "nvarchar(5)");
tableDefinition.Add("MetaKeywords", string.Format("nvarchar({0})", maxStringLength));
tableDefinition.Add("MetaDescription", string.Format("nvarchar({0})", maxStringLength));
tableDefinition.Add("MetaTitle", string.Format("nvarchar({0})", maxStringLength));
tableDefinition.Add("AllowCustomerReviews", "nvarchar(5)");
tableDefinition.Add("AllowCustomerRatings", "nvarchar(5)");
tableDefinition.Add("Published", "nvarchar(5)");
tableDefinition.Add("SKU", string.Format("nvarchar(200)", maxStringLength));
tableDefinition.Add("ManufacturerPartNumber", string.Format("nvarchar({0})", maxStringLength));
tableDefinition.Add("IsDownload", "nvarchar(5)");
tableDefinition.Add("DownloadID", "int");
tableDefinition.Add("UnlimitedDownloads", "nvarchar(5)");
tableDefinition.Add("MaxNumberOfDownloads", "int");
tableDefinition.Add("HasSampleDownload", "nvarchar(5)");
tableDefinition.Add("SampleDownloadID", "int");
tableDefinition.Add("IsShipEnabled", "nvarchar(5)");
tableDefinition.Add("IsFreeShipping", "nvarchar(5)");
tableDefinition.Add("AdditionalShippingCharge", "decimal");
tableDefinition.Add("IsTaxExempt", "nvarchar(5)");
tableDefinition.Add("TaxCategoryID", "int");
tableDefinition.Add("ManageInventory", "nvarchar(5)");
tableDefinition.Add("StockQuantity", "int");
tableDefinition.Add("MinStockQuantity", "int");
tableDefinition.Add("LowStockActivityID", "int");
tableDefinition.Add("NotifyAdminForQuantityBelow", "int");
tableDefinition.Add("OrderMinimumQuantity", "int");
tableDefinition.Add("OrderMaximumQuantity", "int");
tableDefinition.Add("DisableBuyButton", "nvarchar(5)");
tableDefinition.Add("Price", "decimal");
tableDefinition.Add("OldPrice", "decimal");
tableDefinition.Add("Weight", "decimal");
tableDefinition.Add("Length", "decimal");
tableDefinition.Add("Width", "decimal");
tableDefinition.Add("Height", "decimal");
tableDefinition.Add("CreatedOn", "datetime");
excelHelper.WriteTable("Products", tableDefinition);
foreach (Product p in products)
{
ProductVariantCollection productVariants = p.ProductVariants;
foreach (ProductVariant pv in productVariants)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Products] (Name, ShortDescription,FullDescription,ProductTypeID,TemplateID,ShowOnHomePage,MetaKeywords,MetaDescription,MetaTitle,AllowCustomerReviews,AllowCustomerRatings,Published,SKU,ManufacturerPartNumber,IsDownload,DownloadID,UnlimitedDownloads,MaxNumberOfDownloads,HasSampleDownload,SampleDownloadID,IsShipEnabled,IsFreeShipping,AdditionalShippingCharge,IsTaxExempt,TaxCategoryID,ManageInventory,StockQuantity,MinStockQuantity,LowStockActivityID,NotifyAdminForQuantityBelow,OrderMinimumQuantity,OrderMaximumQuantity,DisableBuyButton,Price,OldPrice,Weight, Length, Width, Height, CreatedOn) VALUES (");
string name = p.Name;
if (name.Length > maxStringLength)
name = name.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(name.Replace('"', '\'')); sb.Append("\",");
string shortDescription = p.ShortDescription;
if (shortDescription.Length > maxStringLength)
shortDescription = shortDescription.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(shortDescription.Replace('"', '\'')); sb.Append("\",");
string fullDescription = p.FullDescription;
if (fullDescription.Length > maxStringLength)
fullDescription = fullDescription.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(fullDescription.Replace('"', '\'')); sb.Append("\",");
sb.Append(p.ProductTypeID); sb.Append(",");
sb.Append(p.TemplateID); sb.Append(",");
sb.Append('"'); sb.Append(p.ShowOnHomePage); sb.Append("\",");
string metaKeywords = p.MetaKeywords;
if (metaKeywords.Length > maxStringLength)
metaKeywords = metaKeywords.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(metaKeywords.Replace('"', '\'')); sb.Append("\",");
string metaDescription = p.MetaDescription;
if (metaDescription.Length > maxStringLength)
metaDescription = metaDescription.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(metaDescription.Replace('"', '\'')); sb.Append("\",");
string metaTitle = p.MetaTitle;
if (metaTitle.Length > maxStringLength)
metaTitle = metaTitle.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(metaTitle.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(p.AllowCustomerReviews); sb.Append("\",");
sb.Append('"'); sb.Append(p.AllowCustomerRatings); sb.Append("\",");
sb.Append('"'); sb.Append(p.Published); sb.Append("\",");
string SKU = pv.SKU;
if (SKU.Length > maxStringLength)
SKU = SKU.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(SKU.Replace('"', '\'')); sb.Append("\",");
string manufacturerPartNumber = pv.ManufacturerPartNumber;
if (manufacturerPartNumber.Length > maxStringLength)
manufacturerPartNumber = manufacturerPartNumber.Substring(0, maxStringLength);
sb.Append('"'); sb.Append(manufacturerPartNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(pv.IsDownload); sb.Append("\",");
sb.Append(pv.DownloadID); sb.Append(",");
sb.Append('"'); sb.Append(pv.UnlimitedDownloads); sb.Append("\",");
sb.Append(pv.MaxNumberOfDownloads); sb.Append(",");
sb.Append('"'); sb.Append(pv.HasSampleDownload); sb.Append("\",");
sb.Append(pv.SampleDownloadID); sb.Append(",");
sb.Append('"'); sb.Append(pv.IsShipEnabled); sb.Append("\",");
sb.Append('"'); sb.Append(pv.IsFreeShipping); sb.Append("\",");
sb.Append(pv.AdditionalShippingCharge); sb.Append(",");
sb.Append('"'); sb.Append(pv.IsTaxExempt); sb.Append("\",");
sb.Append(pv.TaxCategoryID); sb.Append(",");
sb.Append('"'); sb.Append(pv.ManageInventory); sb.Append("\",");
sb.Append(pv.StockQuantity); sb.Append(",");
sb.Append(pv.MinStockQuantity); sb.Append(",");
sb.Append(pv.LowStockActivityID); sb.Append(",");
sb.Append(pv.NotifyAdminForQuantityBelow); sb.Append(",");
sb.Append(pv.OrderMinimumQuantity); sb.Append(",");
sb.Append(pv.OrderMaximumQuantity); sb.Append(",");
sb.Append('"'); sb.Append(pv.DisableBuyButton); sb.Append("\",");
sb.Append(pv.Price); sb.Append(",");
sb.Append(pv.OldPrice); sb.Append(",");
sb.Append(pv.Weight); sb.Append(",");
sb.Append(pv.Length); sb.Append(",");
sb.Append(pv.Width); sb.Append(",");
sb.Append(pv.Height); sb.Append(",");
sb.Append('"'); sb.Append(pv.CreatedOn); sb.Append("\"");
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
}
/// <summary>
/// Export charge types to XLS
/// </summary>
/// <param name="filePath">File path to use</param>
/// <param name="orders">Orders</param>
public void ExportChargeTypeToXls(string filePath, List<ChargeType> charges)
{
using (ExcelHelper excelHelper = new ExcelHelper(filePath))
{
excelHelper.Hdr = "YES";
excelHelper.Imex = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
tableDefinition.Add("ChargeTypeID", "int");
tableDefinition.Add("Name", "nvarchar(50)");
tableDefinition.Add("Description", "nvarchar(255)");
tableDefinition.Add("IsActive", "bit");
excelHelper.WriteTable("ChargeType", tableDefinition);
foreach (var charge in charges)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [ChargeType] (ChargeTypeID, Name, Description,IsActive) VALUES (");
sb.Append(charge.ChargeTypeID); sb.Append(",");
sb.Append('"'); sb.Append(charge.Name); sb.Append('"'); sb.Append(",");
sb.Append('"'); sb.Append(charge.Description); sb.Append('"'); sb.Append(",");
sb.Append(charge.IsActive);
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
/// <summary>
/// Export orders to XLS
/// </summary>
/// <param name="FilePath">File path to use</param>
/// <param name="orders">Orders</param>
public static void ExportOrdersToXLS(string FilePath, OrderCollection orders)
{
using (ExcelHelper excelHelper = new ExcelHelper(FilePath))
{
excelHelper.HDR = "YES";
excelHelper.IMEX = "0";
Dictionary<string, string> tableDefinition = new Dictionary<string, string>();
tableDefinition.Add("OrderID", "int");
tableDefinition.Add("OrderGUID", "uniqueidentifier");
tableDefinition.Add("OrderSubtotalInclTax", "decimal");
tableDefinition.Add("OrderSubtotalExclTax", "decimal");
tableDefinition.Add("OrderShippingInclTax", "decimal");
tableDefinition.Add("OrderShippingExclTax", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeInclTax", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeExclTax", "decimal");
tableDefinition.Add("OrderTax", "decimal");
tableDefinition.Add("OrderTotal", "decimal");
tableDefinition.Add("OrderDiscount", "decimal");
tableDefinition.Add("OrderSubtotalInclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderSubtotalExclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderShippingInclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderShippingExclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeInclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("PaymentMethodAdditionalFeeExclTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderTaxInCustomerCurrency", "decimal");
tableDefinition.Add("OrderTotalInCustomerCurrency", "decimal");
tableDefinition.Add("CustomerCurrencyCode", "nvarchar(5)");
tableDefinition.Add("OrderWeight", "decimal");
tableDefinition.Add("AffiliateID", "int");
tableDefinition.Add("OrderStatusID", "int");
tableDefinition.Add("PaymentMethodID", "int");
tableDefinition.Add("PaymentMethodName", "nvarchar(100)");
tableDefinition.Add("PurchaseOrderNumber", "nvarchar(100)");
tableDefinition.Add("PaymentStatusID", "int");
tableDefinition.Add("BillingFirstName", "nvarchar(100)");
tableDefinition.Add("BillingLastName", "nvarchar(100)");
tableDefinition.Add("BillingPhoneNumber", "nvarchar(50)");
tableDefinition.Add("BillingEmail", "nvarchar(255)");
tableDefinition.Add("BillingFaxNumber", "nvarchar(50)");
tableDefinition.Add("BillingCompany", "nvarchar(100)");
tableDefinition.Add("BillingAddress1", "nvarchar(100)");
tableDefinition.Add("BillingAddress2", "nvarchar(100)");
tableDefinition.Add("BillingCity", "nvarchar(100)");
tableDefinition.Add("BillingStateProvince", "nvarchar(100)");
tableDefinition.Add("BillingZipPostalCode", "nvarchar(100)");
tableDefinition.Add("BillingCountry", "nvarchar(100)");
tableDefinition.Add("ShippingStatusID", "int");
tableDefinition.Add("ShippingFirstName", "nvarchar(100)");
tableDefinition.Add("ShippingLastName", "nvarchar(100)");
tableDefinition.Add("ShippingPhoneNumber", "nvarchar(50)");
tableDefinition.Add("ShippingEmail", "nvarchar(255)");
tableDefinition.Add("ShippingFaxNumber", "nvarchar(50)");
tableDefinition.Add("ShippingCompany", "nvarchar(100)");
tableDefinition.Add("ShippingAddress1", "nvarchar(100)");
tableDefinition.Add("ShippingAddress2", "nvarchar(100)");
tableDefinition.Add("ShippingCity", "nvarchar(100)");
tableDefinition.Add("ShippingStateProvince", "nvarchar(100)");
tableDefinition.Add("ShippingZipPostalCode", "nvarchar(100)");
tableDefinition.Add("ShippingCountry", "nvarchar(100)");
tableDefinition.Add("ShippingMethod", "nvarchar(100)");
tableDefinition.Add("ShippingRateComputationMethodID", "int");
tableDefinition.Add("CreatedOn", "datetime");
excelHelper.WriteTable("Orders", tableDefinition);
foreach (Order order in orders)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO [Orders] (OrderID, OrderGUID, OrderSubtotalInclTax, OrderSubtotalExclTax, OrderShippingInclTax, OrderShippingExclTax, PaymentMethodAdditionalFeeInclTax, PaymentMethodAdditionalFeeExclTax, OrderTax, OrderTotal, OrderDiscount, OrderSubtotalInclTaxInCustomerCurrency, OrderSubtotalExclTaxInCustomerCurrency, OrderShippingInclTaxInCustomerCurrency, OrderShippingExclTaxInCustomerCurrency, PaymentMethodAdditionalFeeInclTaxInCustomerCurrency, PaymentMethodAdditionalFeeExclTaxInCustomerCurrency, OrderTaxInCustomerCurrency, OrderTotalInCustomerCurrency, CustomerCurrencyCode, OrderWeight, AffiliateID, OrderStatusID, PaymentMethodID, PaymentMethodName, PurchaseOrderNumber, PaymentStatusID, BillingFirstName, BillingLastName, BillingPhoneNumber, BillingEmail, BillingFaxNumber, BillingCompany, BillingAddress1, BillingAddress2, BillingCity, BillingStateProvince, BillingZipPostalCode, BillingCountry, ShippingStatusID, ShippingFirstName, ShippingLastName, ShippingPhoneNumber, ShippingEmail, ShippingFaxNumber, ShippingCompany, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingStateProvince, ShippingZipPostalCode, ShippingCountry, ShippingMethod, ShippingRateComputationMethodID, CreatedOn) VALUES (");
sb.Append(order.OrderID); sb.Append(",");
sb.Append('"'); sb.Append(order.OrderGUID); sb.Append("\",");
sb.Append(order.OrderSubtotalInclTax); sb.Append(",");
sb.Append(order.OrderSubtotalExclTax); sb.Append(",");
sb.Append(order.OrderShippingInclTax); sb.Append(",");
sb.Append(order.OrderShippingExclTax); sb.Append(",");
sb.Append(order.PaymentMethodAdditionalFeeInclTax); sb.Append(",");
sb.Append(order.PaymentMethodAdditionalFeeExclTax); sb.Append(",");
sb.Append(order.OrderTax); sb.Append(",");
sb.Append(order.OrderTotal); sb.Append(",");
sb.Append(order.OrderDiscount); sb.Append(",");
sb.Append(order.OrderSubtotalInclTaxInCustomerCurrency); sb.Append(",");
sb.Append(order.OrderSubtotalExclTaxInCustomerCurrency); sb.Append(",");
sb.Append(order.OrderShippingInclTaxInCustomerCurrency); sb.Append(",");
sb.Append(order.OrderShippingExclTaxInCustomerCurrency); sb.Append(",");
sb.Append(order.PaymentMethodAdditionalFeeInclTaxInCustomerCurrency); sb.Append(",");
sb.Append(order.PaymentMethodAdditionalFeeExclTaxInCustomerCurrency); sb.Append(",");
sb.Append(order.OrderTaxInCustomerCurrency); sb.Append(",");
sb.Append(order.OrderTotalInCustomerCurrency); sb.Append(",");
sb.Append('"'); sb.Append(order.CustomerCurrencyCode.Replace('"', '\'')); sb.Append("\",");
sb.Append(order.OrderWeight); sb.Append(",");
sb.Append(order.AffiliateID); sb.Append(",");
sb.Append(order.OrderStatusID); sb.Append(",");
sb.Append(order.PaymentMethodID); sb.Append(",");
sb.Append('"'); sb.Append(order.PaymentMethodName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.PurchaseOrderNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append(order.PaymentStatusID); sb.Append(",");
sb.Append('"'); sb.Append(order.BillingFirstName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingLastName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingPhoneNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingEmail.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingFaxNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingCompany.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingAddress1.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingAddress2.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingCity.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingStateProvince.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingZipPostalCode.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingCountry.Replace('"', '\'')); sb.Append("\",");
sb.Append(order.ShippingStatusID); sb.Append(",");
sb.Append('"'); sb.Append(order.ShippingFirstName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingLastName.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingPhoneNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingEmail.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingFaxNumber.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingCompany.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingAddress1.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingAddress2.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingCity.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingStateProvince.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingZipPostalCode.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.BillingCountry.Replace('"', '\'')); sb.Append("\",");
sb.Append('"'); sb.Append(order.ShippingMethod.Replace('"', '\'')); sb.Append("\",");
sb.Append(order.ShippingRateComputationMethodID); sb.Append(",");
sb.Append('"'); sb.Append(order.CreatedOn); sb.Append("\"");
sb.Append(")");
excelHelper.ExecuteCommand(sb.ToString());
}
}
}
/// <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;
}
}
}
