17

We're switching from an old, outdated point-of-sale system to using Magento 1.7 as our POS exclusively. Not unexpectedly, one of the challenges we're facing is how to get almost 20 years of records from the old system to Mage without catastrophe.

Setting aside the challenge of even migrating customer records, the problem I'm focusing on in this question is how I'm going to migrate historical order data from the old POS to Mage. I'm not 100% sure on exact numbers when it many order records we're talking, but I'd say at least a million.

Here's what I'm thinking in terms of how to approach this:

  1. Figure out exactly how the data needs to be formatted for Magento to play nice with it. Whether we can get it out of the old POS in a format that works is questionable, but let's assume for a moment that this goes well...
  2. Create a .CSV file w/ nicely formatted historical data
  3. Find a way to read that .CSV into Magento's $order object row by row -> save()
  4. Profit!

My problem is that I'm a bit fuzzy on how to approach point 2 & 3, go figure. I can format the data coming out of the old POS however I need, even if it's highly cumbersome and involves Perl, but once I have the .CSV file (or whatever file type would actually work for this process) I'm pretty unclear on how I would feed it in to Magento's order object.

I've done some Googling, and I've come up with examples of people using Mage's order object to import orders programmatically, but little discussion on how they're connecting data sources other than the front end cart to said object. I've been studying a version of the order object:

$id=1; // get Customer Id
$customer = Mage::getModel('customer/customer')->load($id);

$transaction = Mage::getModel('core/resource_transaction');
$storeId = $customer->getStoreId();
$reservedOrderId = Mage::getSingleton('eav/config')->getEntityType('order')->fetchNewIncrementId($storeId);

$order = Mage::getModel('sales/order')
  ->setIncrementId($reservedOrderId)
  ->setStoreId($storeId)
  ->setQuoteId(0)
  ->setGlobal_currency_code('USD')
  ->setBase_currency_code('USD')
  ->setStore_currency_code('USD')
  ->setOrder_currency_code('USD');

// set Customer data
$order->setCustomer_email($customer->getEmail())
  ->setCustomerFirstname($customer->getFirstname())
  ->setCustomerLastname($customer->getLastname())
  ->setCustomerGroupId($customer->getGroupId())
  ->setCustomer_is_guest(0)
  ->setCustomer($customer);

// set Billing Address
$billing = $customer->getDefaultBillingAddress();
$billingAddress = Mage::getModel('sales/order_address')
  ->setStoreId($storeId)
  ->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_BILLING)
  ->setCustomerId($customer->getId())
  ->setCustomerAddressId($customer->getDefaultBilling())
  ->setCustomer_address_id($billing->getEntityId())
  ->setPrefix($billing->getPrefix())
  ->setFirstname($billing->getFirstname())
  ->setMiddlename($billing->getMiddlename())
  ->setLastname($billing->getLastname())
  ->setSuffix($billing->getSuffix())
  ->setCompany($billing->getCompany())
  ->setStreet($billing->getStreet())
  ->setCity($billing->getCity())
  ->setCountry_id($billing->getCountryId())
  ->setRegion($billing->getRegion())
  ->setRegion_id($billing->getRegionId())
  ->setPostcode($billing->getPostcode())
  ->setTelephone($billing->getTelephone())
  ->setFax($billing->getFax());
$order->setBillingAddress($billingAddress);

$shipping = $customer->getDefaultShippingAddress();
$shippingAddress = Mage::getModel('sales/order_address')
  ->setStoreId($storeId)
  ->setAddressType(Mage_Sales_Model_Quote_Address::TYPE_SHIPPING)
  ->setCustomerId($customer->getId())
  ->setCustomerAddressId($customer->getDefaultShipping())
  ->setCustomer_address_id($shipping->getEntityId())
  ->setPrefix($shipping->getPrefix())
  ->setFirstname($shipping->getFirstname())
  ->setMiddlename($shipping->getMiddlename())
  ->setLastname($shipping->getLastname())
  ->setSuffix($shipping->getSuffix())
  ->setCompany($shipping->getCompany())
  ->setStreet($shipping->getStreet())
  ->setCity($shipping->getCity())
  ->setCountry_id($shipping->getCountryId())
  ->setRegion($shipping->getRegion())
  ->setRegion_id($shipping->getRegionId())
  ->setPostcode($shipping->getPostcode())
  ->setTelephone($shipping->getTelephone())
->setFax($shipping->getFax());

$order->setShippingAddress($shippingAddress)
  ->setShipping_method('flatrate_flatrate')
  ->setShippingDescription($this->getCarrierName('flatrate'));

$orderPayment = Mage::getModel('sales/order_payment')
  ->setStoreId($storeId)
  ->setCustomerPaymentId(0)
  ->setMethod('purchaseorder')
  ->setPo_number(' - ');
$order->setPayment($orderPayment);

// let say, we have 2 products
$subTotal = 0;
  $products = array(
  '1001' => array(
  'qty' => 1
  ),
  '1002' ->array(
  'qty' => 3
  ),
);
foreach ($products as $productId=>$product) {
  $_product = Mage::getModel('catalog/product')->load($productId);
  $rowTotal = $_product->getPrice() * $product['qty'];
  $orderItem = Mage::getModel('sales/order_item')
    ->setStoreId($storeId)
    ->setQuoteItemId(0)
    ->setQuoteParentItemId(NULL)
    ->setProductId($productId)
    ->setProductType($_product->getTypeId())
    ->setQtyBackordered(NULL)
    ->setTotalQtyOrdered($product['rqty'])
    ->setQtyOrdered($product['qty'])
    ->setName($_product->getName())
    ->setSku($_product->getSku())
    ->setPrice($_product->getPrice())
    ->setBasePrice($_product->getPrice())
    ->setOriginalPrice($_product->getPrice())
    ->setRowTotal($rowTotal)
    ->setBaseRowTotal($rowTotal);

  $subTotal += $rowTotal;
  $order->addItem($orderItem);
}

$order->setSubtotal($subTotal)
  ->setBaseSubtotal($subTotal)
  ->setGrandTotal($subTotal)
  ->setBaseGrandTotal($subTotal);

$transaction->addObject($order);
$transaction->addCommitCallback(array($order, 'place'));
$transaction->addCommitCallback(array($order, 'save'));
$transaction->save();

So here are my specific questions:

  1. Does this seem like an even remotely sensical approach to this problem? And, if not, how do you think I could approach this issue like less of an idiot?
  2. If this is a sensical approach, do I need a different .CSV for each model called up by the order process? i.e. Mage::getModel('sales/order'), Mage::getModel('sales/order_address'), etc?
  3. Is a .CSV even the way to go?
  4. How would I feed my data in to this object, whether that data's contained within a .CSV or what have you?
  5. How would you go about limiting overhead?

Even if I'm thinking about this in a totally idiotic way and you tell me as much, I really appreciate any input at all.

Thank you, thank you, thank you!

  • 1
    Not significant really to your case, but have a look at a previous question of mine and @BenMarks' answer, which involves parsing CSV in Magento and might be useful. magento.stackexchange.com/questions/232/… – pspahn Nov 15 '13 at 4:47
  • 1
    You might want to look at this for inspiration: github.com/avstudnitz/AvS_FastSimpleImport It mainly focusses on importing products and customers, but it's a fast import system. As you're talking about millions of records, you probably want something speedy. I've used this before to import product CSV files. You just read the CSV file and convert the data to arrays. I haven't tried to extend this module to use orders though. So I have no idea how that will work out. Good luck. – Vicky Mar 24 '14 at 15:27
  • So, would automating Dataflow - Imports for importing orders be a bad idea? From what I've read, it seems to be a solution that is fairly common. – sparecycle May 22 '14 at 18:53
9

Surprised no answers with so many votes/views, so I'll bite:

  1. This would be dependant on the old POS system, massage the data during import.
  2. Familiarize yourself with Varien_Io, particularly Varien_Io_File. Since you'll most likely be dealing with such a large collection of data, keep in mind to use streams such as StreamReadCsv and StreamWriteCsv. More details on a "stream". Without a stream or linear read/write you may run into memory issues with other load/write methods.

With the above said here is an example: (source Atwix.com)

/**
 * Generates CSV file with product's list according to the collection in the $this->_list
 * @return array
 */
public function generateMlnList()
{
    if (!is_null($this->_list)) {
        $items = $this->_list->getItems();
        if (count($items) > 0) {

            $io = new Varien_Io_File();
            $path = Mage::getBaseDir('var') . DS . 'export' . DS;
            $name = md5(microtime());
            $file = $path . DS . $name . '.csv';
            $io->setAllowCreateFolders(true);
            $io->open(array('path' => $path));
            $io->streamOpen($file, 'w+');
            $io->streamLock(true);

            $io->streamWriteCsv($this->_getCsvHeaders($items));
            foreach ($items as $product) {
                $io->streamWriteCsv($product->getData());
            }

            return array(
                'type'  => 'filename',
                'value' => $file,
                'rm'    => true // can delete file after use
            );
        }
    }
}

As for importing orders, this example has helped most: (Source: pastebin)

<?php

require_once 'app/Mage.php';

Mage::app();

$quote = Mage::getModel('sales/quote')
    ->setStoreId(Mage::app()->getStore('default')->getId());

if ('do customer orders') {
    // for customer orders:
    $customer = Mage::getModel('customer/customer')
        ->setWebsiteId(1)
        ->loadByEmail('customer@example.com');
    $quote->assignCustomer($customer);
} else {
    // for guesr orders only:
    $quote->setCustomerEmail('customer@example.com');
}

// add product(s)
$product = Mage::getModel('catalog/product')->load(8);
$buyInfo = array(
    'qty' => 1,
    // custom option id => value id
    // or
    // configurable attribute id => value id
);
$quote->addProduct($product, new Varien_Object($buyInfo));

$addressData = array(
    'firstname' => 'Test',
    'lastname' => 'Test',
    'street' => 'Sample Street 10',
    'city' => 'Somewhere',
    'postcode' => '123456',
    'telephone' => '123456',
    'country_id' => 'US',
    'region_id' => 12, // id from directory_country_region table
);

$billingAddress = $quote->getBillingAddress()->addData($addressData);
$shippingAddress = $quote->getShippingAddress()->addData($addressData);

$shippingAddress->setCollectShippingRates(true)->collectShippingRates()
        ->setShippingMethod('flatrate_flatrate')
        ->setPaymentMethod('checkmo');

$quote->getPayment()->importData(array('method' => 'checkmo'));

$quote->collectTotals()->save();

$service = Mage::getModel('sales/service_quote', $quote);
$service->submitAll();
$order = $service->getOrder();

printf("Created order %s\n", $order->getIncrementId());

With the example you have now will be resource heavy, as there are Mage::getModel(... calls in foreach loops which is bad practice, and will most likely either timeout, or fill up memory rather quickly. Especially if you have this wrapped in another foreach/while.

This...

foreach ($products as $productId=>$product) {
  $_product = Mage::getModel('catalog/product')->load($productId);

Should look like:

$_product = Mage::getModel('catalog/product');
foreach ($products as $productId=>$product) {
  $_product->load($productId);

I would not attempt to try and relate every CSV bits of data to Magento objects. It would be madness and a bit of overkill, keep with resource model entry points of $model->load(EntityId).

Also note if you are attempting to import over 100k+ orders I would be concerned for performance after the large imports as its necessary to keep MySQL tuned to handle such large volumes, not too mention if I'm not mistaken sales objects are still EAV based, and do not perform well under high volume/traffic. There is a reason Magento Enterprise has a Sales Order Archive module to pull old data out of the "transactional" sales order tables to prevent bloated/stale data that isn't needed for taking orders.

To Wrap: I would bring up the requirements and needs of the business to store such large data, if its purely reporting there are better alternatives to suite this than Magento.

3

Thinking about the impact these historic orders would have on the magento/mysql performance plus the fact that any product lines that have been discontinued would need to be imported too then it might be worth considering storing the historic orders along with the customer and products in something like an elasticsearch index and perform a search on demand. i.e. the customer order history page.

1

From creating quote and then creating an order is taking too much time for huge data of order import.

So, I've researched and found the conclusion of huge order import data with mysql query:

  1. I inserted the data into only order tables.

  2. Update the increment_id to recognize magento 1.x, that last order increment_id is this

  3. This query is not creating any quote, invoice and shipment:

    SQL Queries:-

    1. INSERT INTO `sales_flat_order` (state, status, shipping_description, store_id, customer_id, base_discount_invoiced, base_grand_total, base_shipping_amount, base_shipping_invoiced, base_subtotal, base_subtotal_invoiced, base_tax_amount, base_tax_invoiced, base_total_invoiced, base_total_invoiced_cost, base_total_paid, discount_invoiced, grand_total, shipping_amount, shipping_invoiced, subtotal, subtotal_invoiced, tax_amount, tax_invoiced, total_invoiced, total_paid, customer_group_id, increment_id, base_currency_code, global_currency_code, customer_email, customer_firstname, customer_lastname, customer_middlename, order_currency_code, shipping_method, store_currency_code, store_name, created_at, updated_at, total_item_count, hidden_tax_invoiced, base_hidden_tax_invoiced, is_valid) VALUES ("complete", "complete", "Flat Rate - Fixed", 1, 38322,0,225.7,0,0,214.95,214.95,10.75,10.75,225.7, 0,225.7, 0,225.7,0,0,214.95,214.95,10.75,10.75,225.7,225.7, 1,100026111,"CAD","CAD","abc@gmail.com","abc","abc","", "CAD", "flatrate_flatrate", "CAD", "Main Website\nMain Website Store\nOnline Catalog","2012-01-17 00:00:00","2012-01-17 00:00:00",5,0,0,0);

    2. INSERT INTO `sales_flat_order_grid` (entity_id, status, shipping_description, shipping_method, store_id, customer_id, customer_email, total_qty_ordered, base_grand_total, base_total_paid, grand_total, total_paid, increment_id, base_currency_code, order_currency_code, store_name, created_at, updated_at, payment_validated, billing_name, shipping_name) VALUES (5, "complete", "Flat Rate - Fixed", "flatrate_flatrate", 1, 38322,"abc@gmail.com",5,225.7,225.7,225.7,225.7,100026111,"CAD", "CAD", "Main Website\nMain Website Store\nOnline Catalog","2012-01-17 00:00:00","2012-01-17 00:00:00",1,"abc abc","abc abc");

    3. INSERT INTO `sales_flat_order_address` (parent_id, region_id, customer_id, email, region, postcode, lastname, street, city, telephone, country_id, firstname, address_type, middlename, nick_name) VALUES (5,68,38322,"alicjakeller@gmail.com","Manitoba","R3W 1G9","abc","1607 Concordia Ave E","Winnipeg","204 667-5540","CA","abc","billing","","")

    4. INSERT INTO `sales_flat_order_address` (parent_id, region_id, customer_id, email, region, postcode, lastname, street, city, telephone, country_id, firstname, address_type, middlename, nick_name) VALUES (5,68,38322,"alicjakeller@gmail.com","Manitoba","R3W 1G9","abc","1607 Concordia Ave E","Winnipeg","204 667-5540","CA","abc","shipping","","");

    5. INSERT INTO `sales_flat_order_item` (order_id, store_id, created_at, updated_at, product_id, product_type, sku, name, qty_ordered, price, base_price, original_price, base_original_price, row_total, base_row_total, price_incl_tax, base_price_incl_tax, row_total_incl_tax, base_row_total_incl_tax) VALUES (5,1,"2012-01-17 00:00:00","2012-01-17 00:00:00",4134,"simple","MET2240","ULTRA FLORA IB - 30 CAPS",4,44.99,44.99,44.99,44.99,179.96,179.96,44.99,44.99,179.96,179.96);

    6. INSERT INTO `sales_flat_order_item` (order_id, store_id, created_at, updated_at, product_id, product_type, sku, name, qty_ordered, price, base_price, original_price, base_original_price, row_total, base_row_total, price_incl_tax, base_price_incl_tax, row_total_incl_tax, base_row_total_incl_tax) VALUES (5,1,"2012-01-17 00:00:00","2012-01-17 00:00:00",3198,"simple","WS1600","THYROSENSE - 180 VCAPS + 60 VCAPS FREE",1,34.99,34.99,34.99,34.99,34.99,34.99,34.99,34.99,34.99,34.99);

    7. INSERT INTO `sales_flat_order_payment` (parent_id, base_shipping_amount, shipping_amount, base_amount_paid, amount_paid, base_amount_ordered, amount_ordered, method) VALUES (5,0,0,225.7,225.7,225.7,225.7, "cashondelivery");

    8. UPDATE `eav_entity_store` SET increment_last_id = 100026111 WHERE `entity_type_id` = 5 AND `store_id` = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.