导出订单明细表magento [英] Exporting order details magento

问题描述

我希望从要迁移的magento系统中导出所有订单的订单信息. 这些是我需要的字段

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.

如果您可以帮助我编写SQL来导出数据，这将很有帮助.

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

我已经有一个SQL.您能帮我完善一下以获得所需的详细信息吗?

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.

推荐答案

最好编写一个脚本，使脚本从magento调用而不是从数据库生成CSV或XML，因为将有许多由键链接的表. (将其分类为一个文件需要进行大量工作，Magento会通过API或标准调用为您完成此工作)

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)

这是我们用于导出订单以拥有XML文件的脚本，将其放在Magento文件夹中并运行.

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

您可以更改XML节点的语法，或将其重写为CSV或其他格式:

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

这篇关于导出订单明细表magento的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！