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