6

I have added sku column on sales order grid using following steps,

Package\Company\view\adminhtml\ui_component\sales_order_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="sku" class="Krish\Customerabandoned\Ui\Component\Listing\Column\Sku">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="label" xsi:type="string" translate="true">Sku</item>

                </item>
            </argument>
        </column>
    </columns>
</listing>

Package\Company\Ui\Component\Listing\Column\Sku.php

public function prepareDataSource(array $dataSource)
{
if (isset($dataSource['data']['items'])) {
foreach ($dataSource['data']['items'] as & $item) {

    $order1  = $this->_orderRepository->get($item["entity_id"]);
    $items123 = $order1->getAllItems();
    $skuArray = [];

    foreach ($items123 as $key => $item1) {
        $skuArray[] = $item1->getSku();}
    $export_status = implode (", ", $skuArray);
$item[$this->getData('name')] = $export_status;
}
}

return $dataSource;
}
}

The sku field displaying in order grid but not showing when I export file to csv.

ScreenShot: enter image description here

  • Fixed this issue?? if yes please post your answer. – Magento2 Devloper Oct 7 '17 at 5:26
  • @paras sakariya Do you know how do you add it to export the column sku cause is empty when export to CSV it only populate the grid – Juliano Vargas May 9 '18 at 17:04
2

To add SKUs Column in order Ui grid. You need to modify below files.

TO add column, add below code in your module

[vendor]/[module]/view/adminhtml/ui_component/sales_order_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
    <listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <columns name="sales_order_columns">
        <column name="skus">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="sortable" xsi:type="boolean">false</item>
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="label" xsi:type="string" translate="true">Skus</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

[vendor]/[module]/etc/adminhtml/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <type name="Magento\Sales\Model\ResourceModel\Order\Grid\Collection">
        <plugin name="ordercolumn_grid_load_before" type="[vendor]\[module]\Plugin\Admin\Order\Grid" sortOrder="10" disabled="false"/>
    </type>
</config>

and then Create a plugin file.

[vendor]/[module]/Plugin/Admin/Order/Grid.php

<?php
namespace [vendor]\[module]\Plugin\Admin\Order;

use Magento\Backend\Model\Auth\Session;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection;
use Magento\User\Model\ResourceModel\User\Collection as UserCollection;

class Grid extends \Magento\Framework\Data\Collection
{
    protected $coreResource;

    protected $adminUsers;

    public function __construct(
        EntityFactoryInterface $entityFactory,
        ResourceConnection $coreResource,
        UserCollection $adminUsers
    ) {
        parent::__construct($entityFactory);
        $this->coreResource = $coreResource;
        $this->adminUsers = $adminUsers;
    }

    public function beforeLoad($printQuery = false, $logQuery = false)
    {
        if ($printQuery instanceof Collection) {
            $collection = $printQuery;

            $joined_tables = array_keys(
                $collection->getSelect()->getPart('from')
            );

                $collection->getSelect()
                    ->columns(
                        array(
                            'skus' => new \Zend_Db_Expr('(SELECT GROUP_CONCAT(`sku` SEPARATOR " & ") FROM `sales_order_item` WHERE `sales_order_item`.`order_id` = main_table.`entity_id` GROUP BY `sales_order_item`.`order_id`)')
                        )
                    );

        }
    }
}
  • Great! I was looking for dynamic solution like this. – Saphal Jha Jul 31 at 7:31
1

I found solution for this,

First require to add sku column on sales grid as per describe in question, To display also in csv and xml file, we need to override following two files,

  1. /vendor/magento/module-ui/Model/Export/ConvertToCsv.php
  2. /vendor/magento/module-ui/Model/Export/ConvertToXml.php

Now, we need to add custom code on function getCsvFile() as per below,

public function getCsvFile()
{
    $component = $this->filter->getComponent();


    $name = md5(microtime());
    $file = 'export/'. $component->getName() . $name . '.csv';

    $this->filter->prepareComponent($component);
    $this->filter->applySelectionOnTargetProvider();
    $dataProvider = $component->getContext()->getDataProvider();
    //exit(get_class($dataProvider));
    $fields = $this->metadataProvider->getFields($component);

    $options = $this->metadataProvider->getOptions();

    $this->directory->create('export');
    $stream = $this->directory->openFile($file, 'w+');
    $stream->lock();
    $stream->writeCsv($this->metadataProvider->getHeaders($component));
    $i = 1;
    $searchCriteria = $dataProvider->getSearchCriteria()
        ->setCurrentPage($i)
        ->setPageSize($this->pageSize);
    $totalCount = (int) $dataProvider->getSearchResult()->getTotalCount();
    while ($totalCount > 0) {
        $items = $dataProvider->getSearchResult()->getItems();
        //  echo '<pre>'; print_r(get_class($dataProvider)); exit;
        foreach ($items as $item) {
            if($component->getName()=='sales_order_grid') {
                $objectManager = \Magento\Framework\App\ObjectManager::getInstance();
                $order = $objectManager->create('Magento\Sales\Model\Order')->load($item->getEntityId());
                $items123 = $order->getAllItems();
                $skuArray = [];

                foreach ($items123 as $key => $item1) {
                    $skuArray[] = $item1->getSku();

                }
                $export_status = implode(", ", $skuArray);
                $item->setSku($export_status);
            }
            $this->metadataProvider->convertDate($item, $component->getName());
            $stream->writeCsv($this->metadataProvider->getRowData($item, $fields, $options));
        }
        $searchCriteria->setCurrentPage(++$i);
        $totalCount = $totalCount - $this->pageSize;
    }
    $stream->unlock();
    $stream->close();

    return [
        'type' => 'filename',
        'value' => $file,
        'rm' => true  // can delete file after use
    ];
}
  • both files same code? – Nadh Aug 8 at 9:13

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.