11

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
9

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

        }
    }
}
| improve this answer | |
  • Great! I was looking for dynamic solution like this. – Saphal Jha Jul 31 '19 at 7:31
2

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
    ];
}
| improve this answer | |
  • both files same code? – User0434 Aug 8 '19 at 9:13
2

I think the best solution is to make a preference for \Magento\Sales\Ui\Component\DataProvider\Document and override getCustomAttribute($attributeCode) method

Example file (\Magento\Sales\Ui\Component\DataProvider\Document):

    /**
     * @inheritdoc
     */
    public function getCustomAttribute($attributeCode)
    {
        switch ($attributeCode) {
            case self::$stateAttributeCode:
                $this->setStateValue();
                break;
            case self::$customerGroupAttributeCode:
                $this->setCustomerGroupValue();
                break;
        }

        return parent::getCustomAttribute($attributeCode);
    }

    /**
     * Update invoice state value
     * Method set text label instead id value
     * @return void
     */
    private function setStateValue()
    {
        $value = $this->getData(self::$stateAttributeCode);
        /** @var \Magento\Framework\Phrase $state */
        $state = Invoice::getStates()[$value];

        $this->setCustomAttribute(self::$stateAttributeCode, $state->getText());
    }

    /**
     * Update customer group value
     * Method set group code instead id value
     * @return void
     */
    private function setCustomerGroupValue()
    {
        $value = $this->getData(self::$customerGroupAttributeCode);
        try {
            $group = $this->groupRepository->getById($value);
            $this->setCustomAttribute(self::$customerGroupAttributeCode, $group->getCode());
        } catch (NoSuchEntityException $e) {
            $this->setCustomAttribute(self::$customerGroupAttributeCode, 'N/A');
        }
    }
| improve this answer | |

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.