private IEnumerable <OrderModel> ReadOrdersImportFile(string ordersImportFile, string productOptionsFile)
{
ReportProgress("Reading " + ordersImportFile);
var ordersToImport = new List <OrderModel>();
var orderSheet = new Worksheet();
orderSheet.Load(ordersImportFile, "Orders", "Order Number");
var lineItemsSheet = new Worksheet();
lineItemsSheet.Load(ordersImportFile, "Order Line Items", "Order Number");
// load all of product options into data structure since we access it twice for each row in the order list
ReportProgress("Reading " + productOptionsFile);
var optionSheet = new Worksheet();
optionSheet.Load(productOptionsFile, "ProductOptions", "ProductCode");
RowData variant = null;
List <string> optionValues = new List <string>();
Dictionary <string, OptionsObj> productVariantMapping = new Dictionary <string, OptionsObj>();
while ((variant = optionSheet.NextRow()) != null)
{
OptionsObj options = new OptionsObj();
options.ProductCode = variant.Cell("ProductCode");
options.Size = variant.Cell("Size");
options.Color = variant.Cell("Color");
productVariantMapping.Add(variant.Cell("VariationCode"), options);
}
RowData orderRow = null;
var maxOrders = 50000;
var orderCount = 1;
while ((orderRow = orderSheet.NextRow()) != null & orderCount < maxOrders)
{
orderCount++;
OrderModel orderModel = new OrderModel();
orderModel.Order = new Order();
// this order number is the Volusion order number and needs to be added via the API to "ExternalID"
orderModel.Order.ExternalId = orderRow.Cell("Order Number");
orderModel.Order.OrderNumber = orderRow.Cell("Order Number").ToInt32(0);
orderModel.Order.CustomerAccountId = orderRow.Cell("Customer Account ID").ToInt32(0);
orderModel.Order.IpAddress = orderRow.Cell("IP Address"); // is this right?
orderModel.Order.AcceptedDate = orderRow.Cell("Submitted On").ToDateTime(DateTime.Now);
orderModel.Order.SubmittedDate = orderRow.Cell("Submitted On").ToDateTime(DateTime.Now);
// this was needed in order to set the read-only SubmittedDate
if (orderModel.Order.SubmittedDate != null)
{
orderModel.Order.AuditInfo = new AuditInfo()
{
CreateBy = "volusion_import", CreateDate = orderModel.Order.SubmittedDate, UpdateBy = "volusion_import", UpdateDate = orderModel.Order.SubmittedDate
};
}
orderModel.Order.BillingInfo = new BillingInfo();
//orderModel.Order.BillingInfo.IsSameBillingShippingAddress = true; // how to tell?
orderModel.Order.BillingInfo.PaymentType = orderRow.Cell("Payment Type"); // Is this correct?
orderModel.Order.BillingInfo.StoreCreditCode = "No"; // Is this correct?
orderModel.Order.BillingInfo.BillingContact = new Contact();
//orderModel.Order.BillingInfo.BillingContact.Id = null;
orderModel.Order.BillingInfo.BillingContact.FirstName = orderRow.Cell("Billing Contact First Name");
orderModel.Order.BillingInfo.BillingContact.LastNameOrSurname =
orderRow.Cell("Billing Contact Last Name");
orderModel.Order.BillingInfo.BillingContact.MiddleNameOrInitial = string.Empty;
// where does this come from?
orderModel.Order.BillingInfo.BillingContact.Email = orderRow.Cell("Billing Contact Email");
orderModel.Order.BillingInfo.BillingContact.PhoneNumbers = new Phone();
orderModel.Order.BillingInfo.BillingContact.PhoneNumbers.Home =
orderRow.Cell("Billing Contact Home Phone");
orderModel.Order.BillingInfo.BillingContact.Address = new Address();
orderModel.Order.BillingInfo.BillingContact.Address.Address1 = orderRow.Cell("Billing Address1");
orderModel.Order.BillingInfo.BillingContact.Address.Address2 = orderRow.Cell("Billing Address2");
orderModel.Order.BillingInfo.BillingContact.Address.Address3 = orderRow.Cell("Billing Address3");
orderModel.Order.BillingInfo.BillingContact.Address.Address4 = orderRow.Cell("Billing Address4");
orderModel.Order.BillingInfo.BillingContact.Address.CityOrTown = orderRow.Cell("Billing City");
orderModel.Order.BillingInfo.BillingContact.Address.StateOrProvince = orderRow.Cell("Billing State");
orderModel.Order.BillingInfo.BillingContact.Address.CountryCode = orderRow.Cell("Billing Country");
orderModel.Order.BillingInfo.BillingContact.Address.AddressType = "Residential";
orderModel.Order.BillingInfo.BillingContact.Address.PostalOrZipCode = orderRow.Cell("Billing Postal Code");
////orderModel.Order.BillingInfo.Card = new PaymentCard();
////orderModel.Order.BillingInfo.Card.PaymentOrCardType = orderRow.Cell("Payment Type");
////orderModel.Order.BillingInfo.Card.CardNumberPartOrMask = "notavailable";
////orderModel.Order.BillingInfo.Card.ExpireMonth = 1; // not available
////orderModel.Order.BillingInfo.Card.ExpireYear = 2015; // not available
////orderModel.Order.BillingInfo.Card.IsCardInfoSaved = false; // not available
////orderModel.Order.BillingInfo.Card.IsUsedRecurring = false; // not available
////orderModel.Order.BillingInfo.Card.NameOnCard = "not available";
////orderModel.Order.BillingInfo.Card.PaymentServiceCardId = string.Empty; // not available
// Shippment data
var shipment = new Shipment();
shipment.Id = null; // where does this come from?
shipment.Cost = orderRow.Cell("Shipping Total").ToDecimal(new decimal(0.00));
shipment.CurrencyCode = DefaultCurrencyCode;
shipment.ShippingMethodCode = orderRow.Cell("Shipping Method");
shipment.SignatureRequired = false;
shipment.TrackingNumber = "not available"; // where does this come from?
// Destination Address
shipment.DestinationAddress = new Contact();
//shipment.DestinationAddress.Id = null; // need to hook up later
shipment.DestinationAddress.FirstName = orderRow.Cell("Shipping Contact First Name");
//shipment.DestinationAddress.MiddleNameOrInitial = ""; // where does this come from?
shipment.DestinationAddress.LastNameOrSurname = orderRow.Cell("Shipping Contact Last Name");
shipment.DestinationAddress.Email = orderRow.Cell("Shipping Contact Email").ToLower();
shipment.DestinationAddress.PhoneNumbers = new Phone()
{
Home = orderRow.Cell("Shipping Contact Home Phone")
};
shipment.DestinationAddress.CompanyOrOrganization = ""; // where does this come from?
shipment.DestinationAddress.Address = new Address();
shipment.DestinationAddress.Address.Address1 = orderRow.Cell("Shipping Address1");
shipment.DestinationAddress.Address.Address2 = orderRow.Cell("Shipping Address2");
shipment.DestinationAddress.Address.Address3 = orderRow.Cell("Shipping Address3");
shipment.DestinationAddress.Address.Address4 = orderRow.Cell("Shipping Address4");
shipment.DestinationAddress.Address.AddressType =
(!string.IsNullOrEmpty(shipment.DestinationAddress.CompanyOrOrganization))
? "Commercial"
: "Residential";
shipment.DestinationAddress.Address.CityOrTown = orderRow.Cell("Shipping City");
shipment.DestinationAddress.Address.StateOrProvince = orderRow.Cell("Shipping State");
shipment.DestinationAddress.Address.CountryCode = orderRow.Cell("Shipping Country");
shipment.DestinationAddress.Address.PostalOrZipCode = orderRow.Cell("Shipping Postal Code");
shipment.DestinationAddress.Address.IsValidated = true;
// does making this true cause Mozu to skip validation?
shipment.OriginAddress = shipment.DestinationAddress;
// Add to order
orderModel.Order.Shipments = new List <Shipment>()
{
shipment
};
// Order Totals
orderModel.Order.CurrencyCode = DefaultCurrencyCode; // where does this come from?
orderModel.Order.AmountAvailableForRefund = new Decimal(0.00);
orderModel.Order.AmountRemainingForPayment = new Decimal(0.00);
orderModel.Order.DiscountTotal = orderRow.Cell("Discount Total").ToDecimal(new decimal(0.00));
orderModel.Order.DiscountedSubtotal = orderRow.Cell("Discounted Sub Total").ToDecimal(new decimal(0.00));
orderModel.Order.DiscountedTotal = orderModel.Order.DiscountedSubtotal; // where does this come from
orderModel.Order.FeeTotal = orderRow.Cell("Fee Total").ToDecimal((Decimal)0.00);
orderModel.Order.HandlingAmount = orderRow.Cell("Handling Amount").ToDecimal(new decimal(0.00));
orderModel.Order.HandlingTaxTotal = orderRow.Cell("Handling Tax").ToDecimal(new decimal(0.00));
orderModel.Order.HandlingTotal = orderModel.Order.HandlingAmount + orderModel.Order.HandlingTaxTotal;
orderModel.Order.ItemTaxTotal = orderRow.Cell("Item Tax").ToDecimal((Decimal)0.00);
orderModel.Order.ShippingTotal = orderRow.Cell("Shipping Total").ToDecimal((Decimal)0.00);
orderModel.Order.ShippingSubTotal = orderModel.Order.ShippingTotal;
//orderModel.Order.ShippingTaxTotal = (Decimal) 0.00;
orderModel.Order.Subtotal = orderRow.Cell("Sub Total").ToDecimal((Decimal)0.00);
orderModel.Order.TaxTotal = orderRow.Cell("Tax").ToDecimal((Decimal)0.00);
orderModel.Order.Total = orderRow.Cell("Total").ToDecimal((Decimal)0.00);
orderModel.Order.TotalCollected = orderModel.Order.Total.Value;
// Other
orderModel.Order.Id = null; // can't hard code this
orderModel.Order.Version = "1";
orderModel.Order.IsImport = true;
orderModel.Order.ImportDate = DateTime.Now;
orderModel.Order.SiteId = Context.ApiContext.SiteId; // is this necessary?
orderModel.Order.ChannelCode = "Volusion"; // where does this come from?
orderModel.Order.Email = orderRow.Cell("Billing Contact Email").ToLower();
orderModel.Order.HasDraft = false;
orderModel.Order.IsDraft = false;
orderModel.Order.IsEligibleForReturns = false;
orderModel.Order.IsTaxExempt = false;
// is this Order Type
orderModel.Order.CustomerInteractionType = "Website";
//orderModel.Order.AvailableActions = new List<string>(); // no available actions for order history
orderModel.Order.Adjustment = null; // not available
orderModel.Order.ClosedDate = orderModel.Order.AcceptedDate; // not available
// set completedStatus, fullfillmentStatus and paymentStatus dependent on
var completedStatus = orderRow.Cell("Status");
var fulfillmentStatus = orderRow.Cell("Shipment Status");
var paymentStatus = orderRow.Cell("Payment Status");
var returnStatus = orderRow.Cell("Return Status");
// per Molly, hard code these statuses
// per Sanjay, if the order status is not Completed or Cancelled, then the order is checked against inventory
string commentText = "";
if (completedStatus.ToLower() == "cancelled")
{
orderModel.Order.Status = "Cancelled";
orderModel.Order.FulfillmentStatus = "NotFulfilled";
commentText = "Imported From Volusion Order Number: " + orderModel.Order.ExternalId;
}
else if (completedStatus.ToLower() == "partially returned")
{
orderModel.Order.Status = "Completed";
orderModel.Order.FulfillmentStatus = "Fulfilled";
commentText = "Imported From Volusion Order Number: " + orderModel.Order.ExternalId + " Order was Partially Returned.";
}
else if (completedStatus.ToLower() == "returned")
{
orderModel.Order.Status = "Cancelled";
orderModel.Order.FulfillmentStatus = "NotFulfilled";
commentText = "Imported From Volusion Order Number: " + orderModel.Order.ExternalId + " Order was Returned.";
}
else
{
orderModel.Order.Status = "Completed";
orderModel.Order.FulfillmentStatus = "Fulfilled";
commentText = "Imported From Volusion Order Number: " + orderModel.Order.ExternalId;
}
orderModel.Order.PaymentStatus = "Paid";
orderModel.Order.ReturnStatus = returnStatus;
orderModel.Order.Notes = new List <OrderNote>()
{
new OrderNote()
{
AuditInfo = new AuditInfo(),
Id = "",
Text = commentText
}
};
// Link up to an existing account and contact
if (!_existingAccounts.ContainsKey(orderModel.Order.Email))
{
// throw new Exception(
// string.Format("Unable to import orderhistory for {0} because customer does not exist {1}",
// orderModel.Order.OrderNumber,
// orderModel.Order.Email));
ReportProgress(string.Format("Unable to import orderhistory for \"{0}\" because customer \"{1}\" does not exist.", orderModel.Order.ExternalId, orderModel.Order.Email));
}
else
{
// reset the user account ID to the one we look up based on the matched email
orderModel.Order.CustomerAccountId = _existingAccounts[orderModel.Order.Email].Id;
var account = _existingAccounts[orderModel.Order.Email];
//orderModel.Order.CustomerAccountId = account.Id;
// Find the billing contact or the first one whichever we can get.
// TODO some customers don't have contact information (address)
// CustomerContact billingContact = account.Contacts.FirstOrDefault(contact => contact.Types.Contains(new ContactType() { IsPrimary = true, Name = "Billing" }))
// ?? account.Contacts[0];
//orderModel.Order.BillingInfo.BillingContact.Id = billingContact.Id; // where does this come from?
//orderModel.Order.Shipments[0].DestinationAddress.Id;// where does this come from?
// Other not available or not set
//orderModel.Order.CustomerTaxId = ""; // not available
//orderModel.Order.ExpirationDate; // is this the order expiration date or the cc expiration date?
//orderModel.Order.ExternalId; // where does this come from?
orderModel.Order.LocationCode = "homebase"; // where does this come from?
//orderModel.Order.OriginalCartId = ""; // where does this come from?
//orderModel.Order.Packages;
//orderModel.Order.ParentReturnId;
//orderModel.Order.Payments;
//orderModel.Order.Pickups;
//orderModel.Order.ShippingDiscounts;
//orderModel.Order.SourceDevice; // where does this come from?
//orderModel.Order.TenantId = 6466; // clean test
//orderModel.Order.TenantId = 7927; // order import test
orderModel.Order.TenantId = 6653; // production
//orderModel.Order.ValidationResults;
//orderModel.Order.Version;
//orderModel.Order.VisitId;
//orderModel.Order.WebSessionId;
// Add required collections -- even if they are left empty
orderModel.Order.Attributes = new List <OrderAttribute>();
//orderModel.Order.AuditInfo= new AuditInfo();
orderModel.Order.AvailableActions = new List <string>();
//orderModel.Order.BillingInfo = new BillingInfo(); // no billing info will be imported
orderModel.Order.ChangeMessages = new List <ChangeMessage>();
orderModel.Order.CouponCodes = new List <string>();
orderModel.Order.FulfillmentInfo = new FulfillmentInfo();
orderModel.Order.InvalidCoupons = new List <InvalidCoupon>();
orderModel.Order.OrderDiscounts = new List <AppliedDiscount>();
orderModel.Order.Packages = new List <Package>();
orderModel.Order.Payments = new List <Payment>();
orderModel.Order.Pickups = new List <Pickup>();
orderModel.Order.ShippingDiscounts = new List <ShippingDiscount>();
orderModel.Order.ShopperNotes = new ShopperNotes();
orderModel.Order.ValidationResults = new List <OrderValidationResult>();
orderModel.Order.FulfillmentInfo.ShippingMethodCode = shipment.ShippingMethodCode;
orderModel.Order.FulfillmentInfo.ShippingMethodName = shipment.ShippingMethodCode;
orderModel.Order.FulfillmentInfo.FulfillmentContact = shipment.DestinationAddress;
orderModel.Order.Items = new List <OrderItem>();
IList <RowData> detailData = lineItemsSheet.GetRows(orderModel.Order.OrderNumber.ToString());
var itemCount = 0;
foreach (var row in detailData)
{
var lineItem = new OrderItem();
lineItem.Id = itemCount.ToString();
lineItem.DiscountTotal = row.Cell("Discount Total").ToDecimal(new decimal(0.00));
// detail.DiscountedTotal;
lineItem.ExtendedTotal = row.Cell("Total").ToDecimal(new decimal(0.00));
//detail.FeeTotal;
//detail.FulfillmentLocationCode;
//detail.FulfillmentMethod;
lineItem.IsRecurring = false;
lineItem.IsTaxable = row.Cell("Is Taxable").ToBoolean(true);
lineItem.ItemTaxTotal = row.Cell("Taxable Total").ToDecimal(new decimal(0.00));
lineItem.LocaleCode = "en-US";
//detail.OriginalCartItemId;
lineItem.Product = new Product();
//lineItem.Product.Price = row.Cell("Total").ToDecimal(new ProductPrice());
string variationCode = row.Cell("Product Variation Code");
if (variationCode.Contains("-"))
{
variationCode = variationCode.Substring(variationCode.LastIndexOf("-") + 1, variationCode.Length - variationCode.LastIndexOf("-") - 1);
}
lineItem.Product.VariationProductCode = variationCode;
string productName = row.Cell("Product Name");
lineItem.Product.Name = productName;
if (productVariantMapping.ContainsKey(lineItem.Product.VariationProductCode))
{
// the product code is not on the spreadsheet. We need to look it up given the variation code
//lineItem.Product.ProductCode = row.Cell("Product Code");
setProductCode(lineItem, productVariantMapping);
// detail.ProductDiscount;
//detail.ProductDiscounts;
setProductOptions(lineItem, productVariantMapping);
}
else
{
// hard code ProductCode to 0000
lineItem.Product.ProductCode = "0000";
// set size and color to unknown
setColorAndSize(lineItem, "unknown", "unknown");
//ReportProgress(string.Format("Unable to add line item for product \"{1}\" with ID of \"{0}\" because item doesn't exist in import spreasheet.", orderModel.Order.ExternalId, lineItem.Product.VariationProductCode));
}
lineItem.Quantity = row.Cell("Quantity").ToInt32(0);
//detail.ShippingDiscounts;
//detail.ShippingTotal;
lineItem.Subtotal = row.Cell("Sub Total").ToDecimal(new decimal(0.00));
lineItem.TaxableTotal = row.Cell("Taxable Total").ToDecimal(new decimal(0.00));
lineItem.Total = row.Cell("Total").ToDecimal(new decimal(0.00));
// detail.UnitPrice;
if (lineItem.Quantity == 0)
{
// ReportProgress(string.Format("Unable to add line item for product \"{1}\" with ID of \"{0}\" because item quantity is zero.", orderModel.Order.ExternalId, lineItem.Product.VariationProductCode));
lineItem.Quantity = 1;
orderModel.Order.Status = "Cancelled";
orderModel.Order.FulfillmentStatus = "NotFulfilled";
}
orderModel.Order.Items.Add(lineItem);
itemCount++;
}
// only add the order Model if at least one line item has a quantity > 0
if (itemCount > 0)
{
ordersToImport.Add(orderModel);
}
}
}
return(ordersToImport);
}
private IEnumerable <CustomerModel> ReadCustomerImportFile(string customerImportFile)
{
var customersToImport = new List <CustomerModel>();
var customerAccounts = new Worksheet();
customerAccounts.Load(customerImportFile, "CustomerAccounts", "AccountID");
var customerContacts = new Worksheet();
customerContacts.Load(customerImportFile, "CustomerContacts", "AccountId");
RowData row = null;
while ((row = customerAccounts.NextRow()) != null)
{
var customer = new CustomerModel();
customer.Account = new CustomerAccount();
customer.Account.Id = row.Cell("AccountID").ToInt32(0);
customer.Account.UserName = row.Cell("UserName");
customer.Account.EmailAddress = row.Cell("Email");
customer.Account.FirstName = row.Cell("FirstName");
customer.Account.LastName = row.Cell("LastNameOrSurname");
customer.Account.CompanyOrOrganization = row.Cell("CompanyOrOrganization");
customer.Account.TaxExempt = row.Cell("TaxExempt").ToBoolean(false);
customer.Account.TaxId = row.Cell("TaxId");
customer.Account.AcceptsMarketing = row.Cell("AcceptsMarketing").ToBoolean(false);
customer.Account.LocaleCode = row.Cell("LocaleCode");
//customer.Account.UserRole = row.Cell("UserRole");
//customer.Account.IsActive = row.Cell("IsActive").ToBoolean(true);
customer.Account.IsAnonymous = row.Cell("IsAnonymous").ToBoolean(false);
//customer.Account.TotalOrderAmount = row.Cell("Total Order Amount").ToDecimal(new decimal(0.00));
//customer.Account.TotalOrders = row.Cell("Total Orders").ToInt32(0);
//customer.Account.LastOrderDate = DateTime.MinValue;
//customer.Account.TotalContacts = 0;
customer.Password = row.Cell("Password");
// Possible Delete
if (customerAccounts.HasColumn("Delete"))
{
customer.Delete = row.Cell("Delete").ToBoolean(false);
}
customersToImport.Add(customer);
IList <RowData> contactRows = customerContacts.GetRows(customer.Account.Id.ToString());
foreach (var contactRow in contactRows)
{
// Populate the CustomerContact
var contact = new CustomerContact();
contact.AccountId = customer.Account.Id;
contact.Types = new List <ContactType>()
{
new ContactType()
{
IsPrimary = true, Name = "Billing"
}
};
contact.FirstName = contactRow.Cell("FirstName");
contact.LastNameOrSurname = contactRow.Cell("LastNameOrSurname");
contact.MiddleNameOrInitial = contactRow.Cell("MiddleNameorInitial");
contact.CompanyOrOrganization = contactRow.Cell("CompanyOrOrganization");
contact.Email = contactRow.Cell("Email");
contact.FaxNumber = contactRow.Cell("FaxNumber");
contact.PhoneNumbers = new Phone()
{
Home = contactRow.Cell("HomePhone"),
Mobile = contactRow.Cell("MobilePhone"),
Work = contactRow.Cell("WorkPhone")
};
contact.Address = new Address()
{
Address1 = contactRow.Cell("Address1"),
Address2 = contactRow.Cell("Address2"),
Address3 = contactRow.Cell("Address3"),
Address4 = contactRow.Cell("Address4"),
AddressType = contactRow.Cell("AddressType"),
CityOrTown = contactRow.Cell("CityOrTown"),
StateOrProvince = contactRow.Cell("StateOrProvince"),
CountryCode = contactRow.Cell("PostalOrZipCode"),
PostalOrZipCode = contactRow.Cell("CountryCode"),
IsValidated = true
};
customer.Contacts.Add(contact);
}
}
return(customersToImport);
}