Exporting order details magento

Question

I am looking to export order information for all the orders from a magento system i want to migrate from. These are the fields I require

Order_id, sku, item_quantity, item_price, order_total_amount,  Created_at, Billing_address, Billing_city, billing_state, billing_country, billing_zipcode, billing_customer_name, billing_customer_mobile, billing_customer_email, shipping_address, shipping_city, Shipping_state, shipping_zipcode, Shipping_country, shipping_charge, shipping_customer_name, shipping_customer_mobile, order_status

I have tried a few free extensions but they do not solve my purpose.

It would be helpful if you can help me write a SQL to export the data out.

I already have a SQL. Could you help me refine this to get the details i need

SELECT sfo.entity_id, sfo.status, sfo.customer_email, oi.product_id, oi.name, 
oi.price, sfo.total_due, billing.firstname, billing.lastname, billing.street,
billing.city, billing.postcode, billing.country_id, billing.telephone, shipping.firstname,
shipping.lastname, shipping.street, shipping.city, shipping.postcode, shipping.country_id,
shipping.telephone, sfo.store_name, sfo.store_currency_code, sfo.created_at
FROM sales_flat_order AS sfo
JOIN sales_flat_order_address AS billing ON billing.parent_id=sfo.entity_id AND billing.address_type='billing'
JOIN sales_flat_order_address AS shipping ON shipping.parent_id=sfo.entity_id AND shipping.address_type='shipping'
JOIN sales_flat_order_item as oi ON oi.order_id=sfo.entity_id

I also need to handle the case where one order can include multiple items.

Drum

You should not use direct sql querys in magento! You're looking for an order collection: Call Mage::getModel('sales/order')->getCollection(); and iterate it in a foreach-loop to get the single object property for each order. – Ivan

@StefanBrendle I have yet to get consistent results from collections with regard to sales. Most consistent results I've gotten come from sales_flat_order, sales_flat_invoice, and sales_flat_invoice_item. I go on a bit in my answer here: magento.stackexchange.com/questions/40084/… – Abirritate

Answer 1

It's better to write a script that makes CSV or XML from magento calls, not from the database, as there will be a lot of tables chained by keys. (a lot of work to sort it to one file, magento does it for you in API or in standard calls)

This is script we using for export orders to own XML file, put it to the Magento folder and run.

You can change syntax of XML nodes, or rewrite it for CSV or anything else:

require_once("../app/Mage.php");
umask(0);
Mage::app("default");

error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
Mage::init();

// Set an Admin Session
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
Mage::getSingleton('core/session', array('name' => 'adminhtml'));
$userModel = Mage::getModel('admin/user');
$userModel->setUserId(1);
$session = Mage::getSingleton('admin/session');
$session->setUser($userModel);
$session->setAcl(Mage::getResourceModel('admin/acl')->loadAcl());

$connection = Mage::getSingleton('core/resource')->getConnection('core_write');

/* Get orders collection of pending orders, run a query */
        $collection = Mage::getModel('sales/order')
                        ->getCollection()
                //      ->addFieldToFilter('state',Array('eq'=>Mage_Sales_Model_Order::STATE_NEW))
                        ->addAttributeToSelect('*');

$out = '<?xml version="1.0" encoding="windows-1250" ?>
<dat:dataPack id="order001" version="2.0" note="Import Order">';

foreach($collection as $order)
{

     if ($billingAddress = $order->getBillingAddress()){
            $billingStreet = $billingAddress->getStreet();
        }
        if ($shippingAddress = $order->getShippingAddress()){
            $shippingStreet = $shippingAddress->getStreet();
        }

    $out .= "<dat:dataPackItem  version=\"2.0\">\n";
    //$out .= "<dat:dataPackItemversion=\"1.0\">\n";
            $out.= "<ord:order>\n";

                $out.= "<ord:orderHeader>\n";
                    $out.= "<ord:orderType>receivedOrder</ord:orderType>\n";
                    $out.= "<ord:numberOrder>".$order->getIncrementId()."</ord:numberOrder>\n";
                    $out.= "<ord:date>".date('Y-m-d',strtotime($order->getCreatedAt()))."</ord:date>\n";
                    $out.= "<ord:dateFrom>".date('Y-m-d',strtotime($order->getCreatedAt()))."</ord:dateFrom>\n";
                    $out.= "<ord:dateTo>".date('Y-m-d',strtotime($order->getCreatedAt()))."</ord:dateTo>\n";
                    $out.= "<ord:text>Objednávka z internetového obchodu</ord:text>\n";
                    $out.= "<ord:partnerIdentity>\n";
                        $out.= "<typ:address>\n";
                            $out.= "<typ:company>{$billingAddress->getCompany()}</typ:company>\n";
                            $out.= "<typ:division></typ:division>\n";
                            $out.= "<typ:name>{$billingAddress->getName()}</typ:name>\n";
                            $out.= "<typ:city>{$billingAddress->getCity()}</typ:city>\n";
                            $out.= "<typ:street>{$billingStreet[0]}</typ:street>\n";
                            $out.= "<typ:zip>{$billingAddress->getPostcode()}</typ:zip>\n";
                        $out.= "</typ:address> \n";
                        $out.="<typ:shipToAddress>\n";
                            $out.= "<typ:company>{$shippingAddress->getCompany()}</typ:company>\n";
                            $out.= "<typ:division></typ:division>\n";
                            $out.= "<typ:name>{$shippingAddress->getName()}</typ:name>\n";
                            $out.= "<typ:city>{$shippingAddress->getCity()}</typ:city>\n";
                            $out.= "<typ:street>{$shippingStreet[0]}</typ:street>\n";
                            $out.= "<typ:zip>{$shippingAddress->getPostcode()}</typ:zip>\n";
                        $out.= "</typ:shipToAddress>\n";
                    $out.= "</ord:partnerIdentity>\n";
                    $out.= "<ord:paymentType> \n";
                        $out.= "<typ:ids>{$order->getShippingDescription()}</typ:ids>\n";
                    $out.= "</ord:paymentType>\n";
                    $out.= "<ord:priceLevel>\n";
                        $out.= "<typ:ids></typ:ids>\n";
                    $out.= "</ord:priceLevel>\n";
                $out.= "</ord:orderHeader>\n";
                $out.= "<ord:orderDetail> \n";
                foreach ($order->getAllItems() as $itemId => $item){
                    // textova polozka
                    $out.= "<ord:orderItem> \n";
                        $itemname =  $item->getName();
            $itemname =  str_replace('&', " ", $itemname);
            $out.= "<ord:text>{$itemname}</ord:text> \n";
                        $out.= "<ord:quantity>{$item->getQtyOrdered()}</ord:quantity>\n";
                        //$out.= "<ord:delivered></ord:delivered>";
                        $out.= "<ord:rateVAT>high</ord:rateVAT> \n";
                        $out.= "<ord:homeCurrency> \n";
                            $out.= "<typ:unitPrice>{$item->getPrice()}</typ:unitPrice>\n";
                        $out.= "</ord:homeCurrency>\n";
                        $out.= "<ord:stockItem>\n";
                            $out.= "<typ:stockItem>\n";
                                $out.= "<typ:ids>{$item->getSku()}</typ:ids>\n";
                            $out.= "</typ:stockItem>\n";
                        $out.= "</ord:stockItem>\n";
                    $out.= "</ord:orderItem>\n";
                }
                $out.= "</ord:orderDetail>\n";
                $out.= "<ord:orderSummary>\n";
                    $out.= "<ord:roundingDocument>math2one</ord:roundingDocument>\n";
                $out.= "</ord:orderSummary>\n";
            $out.= "</ord:order>\n";
        $out.= "</dat:dataPackItem>\n\n";
};

$out.= "</dat:dataPack>\n";



header ("Content-Type:text/xml");
header ('char-set: cp1250');
@file_put_contents('./dl/response/'.microtime(true).'.txt', $out);
@file_put_contents('php://output', $out);

Asterism

Any known issues with importing the output of this script back into Magento? – Trichinize

Output will be in XML, it is not possible to import back into Magento from this format – Asterism

note that this code allows arbitrary XML to be injected into the document. It's better to use SimpleXML or DOMDocument to create XML document and then export the results from that object since they take care of properly formatting the data in the nodes for you. – Entertainer

Answer 2

Rewrite Martin (above code) to generate CSV File.

<?php
error_reporting(E_ALL);
ini_set("memory_limit", "100000M");

require_once('app/Mage.php'); //Path to Magento
umask(0);
Mage::app("default");
Mage::init();

// Set an Admin Session
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);
Mage::getSingleton('core/session', array('name' => 'adminhtml'));
$userModel = Mage::getModel('admin/user');
$userModel->setUserId(1);
$session = Mage::getSingleton('admin/session');
$session->setUser($userModel);
$session->setAcl(Mage::getResourceModel('admin/acl')->loadAcl());

$connection = Mage::getSingleton('core/resource')->getConnection('core_write');

$fromDate = date('Y-m-d H:i:s', strtotime('2018-01-01'));
$toDate = date('Y-m-d H:i:s', strtotime('2019-01-01'));

/* Get orders collection of pending orders, run a query */
$collection = Mage::getModel('sales/order')
    ->getCollection()
//      ->addFieldToFilter('state',Array('eq'=>Mage_Sales_Model_Order::STATE_NEW))
    ->addAttributeToSelect('*')
    ->addAttributeToFilter('created_at', array('from' => $fromDate, 'to' => $toDate));
//    ->setPageSize(2)
//    ->setCurPage(1);

$data[0] = array(
    'Order ID',
    'Status',
    'Shipping description',
    'Grand Total',
    'Total Qty Ordered',
    'Customer First name',
    'Customer Last name',
    'Customer Email',
    'Created At',
    'Billing Company',
    'Billing Name',
    'Billing City',
    'Billing Street',
    'Billing POST Code',
    'Shipping Company',
    'Shipping Name',
    'Shipping City',
    'Shipping Street',
    'Shipping POST Code',
    'Item Name',
    'Qty Ordered',
    'Price',
    'Product SKU'
);

foreach ($collection as $order) {

    if ($billingAddress = $order->getBillingAddress()) {
        $billingStreet = $billingAddress->getStreet();
    }
    if ($shippingAddress = $order->getShippingAddress()) {
        $shippingStreet = $shippingAddress->getStreet();
    }

    $orderData = array(
        $order->getIncrementId(),
        $order->getStatus(),
        $order->getShippingDescription(),
        $order->getGrandTotal(),
        $order->getTotalQtyOrdered(),
        $order->getCustomerFirstname(),
        $order->getCustomerLastname(),
        $order->getCustomerEmail(),
        date('Y-m-d', strtotime($order->getCreatedAt())),
        $billingAddress->getCompany(),
        $billingAddress->getName(),
        $billingAddress->getCity(),
        $billingStreet[0],
        $billingAddress->getPostcode(),
        $shippingAddress->getCompany(),
        $shippingAddress->getName(),
        $shippingAddress->getCity(),
        $shippingStreet[0],
        $shippingAddress->getPostcode(),

    );


    foreach ($order->getAllItems() as $itemId => $item) {

        $item_name = str_replace('&', " ", $item->getName());
        $itemData = array(
            $item_name,
            $item->getQtyOrdered(),
            $item->getPrice(),
            $item->getSku()
        );

        $data[] = array_merge($orderData, $itemData);
    }


};

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="productData.csv"');

$fp = fopen('php://output', 'wb');
foreach ($data as $line) {
    fputcsv($fp, $line, ',');
}
fclose($fp);

Vins

this does not include all items of an order ? – Frau

@AshwaniPanwar Its include all items of order – Vins