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.

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