0

I have added one column in my sales invoice grid. My custom column is in sales_invoice table.

How can I override existing sales invoice grid and add my custom joint query.

Thanks in advance

1

Step 1. Add column to sales_order_grid/sales_invoice_grid database table

app/code/VENDOR/MODULE/Setup/InstallSchema.php

Adding following code snippet will do trick for you.

$setup->getConnection()->addColumn(
        $setup->getTable('sales_invoice'),
        'custom_column',
        [
            'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT,
            'length' => 255,
            'nullable' => true,
            'comment' => 'Custom Column'
        ]
    );
$setup->getConnection()->addColumn( 
       $setup->getTable('sales_invoice_grid'), 
       'custom_column', 
       [
           'type' => \Magento\Framework\DB\Ddl\Table::TYPE_TEXT, 
           'length' => 255, 
           'nullable' => true, 
           'comment' => 'Custom Column' 
       ]
   );

Step 2. Map column using DI configuration to populate it in sales_invoice_grid table

The following xml snippet should be added to di configuration inside config node.

<config ...>
    ...
    <virtualType name="Magento\Sales\Model\ResourceModel\Order\Invoice\Grid">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="custom_column" xsi:type="string">
                     sales_invoice.custom_column
                </item>
            </argument>
        </arguments>
    </virtualType>
    ...
</config>

Step 3. Configure UI grid component to display custom column

app/code/VENDOR/MODULE/view/adminhtml/ui_component/sales_order_invoice_grid.xml 
file.

<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_invoice_columns">
    <column name="custom_column">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item>
                <item name="label" xsi:type="string" translate="true">Custom Column</item>
            </item>
        </argument>
    </column>
</columns>

if you want to change collection than override this file in your module with below file.

https://github.com/pepe1518/magento2/blob/master/vendor/magento/module-sales/Model/ResourceModel/Order/Invoice/Collection.php

2
0

I have updated the answer, Try below solution.

Step 1 : app/code/Vendor/Module/view/adminhtml/ui_component/sales_order_invoice_grid.xml

<?xml version="1.0" encoding="UTF-8"?>
<!--
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<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_invoice_columns">
        <column name="your_custom_column">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="visible" xsi:type="boolean">true</item>
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" xsi:type="string" translate="true">Custom Column</item>
                </item>
            </argument>
        </column>
    </columns>
</listing>

Step 2 : app/code/Vendor/Module/etc/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\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <plugin name="sales_invoice_additional_columns" type="Vendor\Module\Plugin\SalesInvoiceCustomColumn" sortOrder="10" disabled="false" />
    </type>
</config>

Step 3 : app/code/Vendor/Module/Plugin/SalesInvoiceCustomColumn.php

<?php
namespace Vendor\Module\Plugin;

use Magento\Framework\Message\ManagerInterface as MessageManager;
use Magento\Sales\Model\ResourceModel\Order\Grid\Collection as SalesOrderGridCollection;

class SalesInvoiceCustomColumn
{
    private $messageManager;
    private $collection;

    public function __construct(
        MessageManager $messageManager,
        SalesOrderGridCollection $collection
    ) {
        $this->messageManager = $messageManager;
        $this->collection = $collection;
    }

    public function aroundGetReport(
        \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
        \Closure $proceed,
        $requestName
    ) {
        $result = $proceed($requestName);
        if ($requestName == 'sales_order_invoice_columns') {
            if ($result instanceof $this->collection
            ) {
                $select = $this->collection->getSelect();
                $select->joinLeft(
                    ["secondTable" => $this->collection->getTable("sales_invoice")],
                    'main_table.entity_id = secondTable.entity_id',
                    array('yopur_custom_column')
                );
                return $this->collection;
            }
        }
        return $result;
    }
}
8
  • When you filter from store wise or order id wise it's not working – Sweety Masmiya Sep 9 '19 at 5:19
  • I have updated, now all the default operations will work – Ranganathan Sep 9 '19 at 6:01
  • When I apply order or store wise filtering then it's not work. – Sweety Masmiya Sep 9 '19 at 8:37
  • I can't get you...please provide a steps to reproduce – Ranganathan Sep 9 '19 at 8:39
  • I am taking about when you apply filtering after adding custom column. prnt.sc/p3ohn4 – Sweety Masmiya Sep 9 '19 at 9:03
0

Try this:

Step 1:

Vendor/Module/view/adminhtml/ui_component/sales_order_invoice_grid.xml

<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_invoice_columns">
        <column name="custom_column_name">
            <settings>
                <label translate="true">Column name</label>
                <visible>true</visible>
                <filter>text</filter>
                <sorting>asc</sorting>
            </settings>
        </column>
    </columns>
</listing>

Step 2:

Vendor/Module/etc/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\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <plugin name="sales_invoice_additional_columns" type="Vendor\Module\Plugin\SalesInvoiceGridCollection" sortOrder="10" disabled="false" />
    </type>
</config>

Step 3:

Vendor/Module/Plugin/SalesInvoiceGridCollection.php

<?php

namespace Vendor\Module\Plugin;

use Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory;
use Magento\Sales\Model\ResourceModel\Order\Invoice\Grid\Collection as SalesOrderInvoiceGridCollection;

class SalesInvoiceGridCollection
{
    const SALES_ORDER_INVOICE_GRID_DATA_SOURCE = 'sales_order_invoice_grid_data_source';

    private $collection;

    public function __construct(SalesOrderInvoiceGridCollection $collection)
    {
        $this->collection = $collection;
    }

    public function aroundGetReport(CollectionFactory $subject, \Closure $proceed, $requestName)
    {
        $result = $proceed($requestName);

        if (self::SALES_ORDER_INVOICE_GRID_DATA_SOURCE == $requestName) {
            if ($result instanceof $this->collection) {
                $select = $this->collection->getSelect();
                $select->joinLeft(
                    ['si' => $this->collection->getTable('sales_invoice')],
                    'main_table.entity_id = si.entity_id',
                    ['custom_column_name']
                );

                return $this->collection;
            }
        }

        return $result;
    }
}

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.