php - 导出订单详细信息 magento

Question

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

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

我尝试了一些免费扩展，但它们并没有解决我的问题。

如果你能帮我写一个SQL把数据导出来就好了。

我已经有一个 SQL。你能帮我改进这个以获得我需要的细节吗

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

我还需要处理一个订单可以包含多个项目的情况。

Answer 1

最好编写一个脚本，从 magento 调用生成 CSV 或 XML，而不是从数据库生成，因为会有很多由键链接的表。 (将它分类到一个文件需要做很多工作，magento 会在 API 或标准调用中为您完成)

这是我们用于将订单导出到自己的 XML 文件的脚本，将其放入 Magento 文件夹并运行。

您可以更改 XML 节点的语法，或将其重写为 CSV 或其他任何内容:

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