Category: Magento

Magento 2 Order Comment on Place Order

This extension will add a comment box in at checkout where Customer can add their Comments, instructions or special requests for that particular order and Admin will able to see it in Order page at the Admin area and take care of it. and give Customer a better shopping experience.

Features :

  • While checkout, customer can leave order comment just before place order.
  • The order comments will be appear on order detail page under the Magento admin comment section.
  • The order comments will be appear on the order details page for the customer at frontnend.

The extension does’t add any tables or columns to the Magento 2.x database. The extension has been designed to work automatically with the default Luma theme of Magento 2.x. No design changes to the front end or back end is required.

See following instruction to install this module. I hope you’ll like it 🙂
Continue reading “Magento 2 Order Comment on Place Order”

Advertisements

Magento 2 Reindex from Admin UI

In Magento 1 there is default functionality to reindex data from backend in the Index Management section. However, this feature is no longer available on Magento 2 admin side. You must have SSH access to reindex data from admin.

I’ve make small module in Magento 2, which  allows admin to reindex data from backend UI at any time without the help from a developer. Admin can also limit the ability reindex data by admin role. I’ve added ACL configuration for this.

See following instruction to install this module. I hope you’ll like it 🙂

Log in to the Magento server, go to your Magento install directory and run following commands:

composer require rohitkundale/module-reindex

php -f bin/magento module:enable RohitKundale_Reindex
php -f bin/magento setup:upgrade

rm -rf pub/static/*; rm -rf var/view_preprocessed/*;
php -f bin/magento setup:static-content:deploy

Go to Admin > System > Tools > Index Management. Then check following image for further instruction to reindex data admin/backend.

Magento 2 reindex from admin

You can also find this module on Github here : https://github.com/rohitkundale/module-reindex. I hope you like it 🙂

In case you feel you need some extra help, feel free to get in touch and see what i can do for you. 🙂

Add column in Sales Invoice/Order grid in Magento 2

Magento 2 UI components are designed for simple and flexible UI rendering of several list and grid types.

Sales order and  invoice grid in Magento 2 can be accessed in admin panel under “Sales” -> “Orders/Invoice” menu. By default only several main columns are visible in grid, but there are additional columns, that can be enabled from “Columns” dropdown on the top-right side.

7cdea-sales_order_grid_optional_columns

If you are going to create a module that provides additional useful information about orders or invoices, it is a good idea to add corresponding columns to sales order grid or sales invoice grid.

Create custom module in Magento 2 before you are going to add custom column in sales order or invoice grid. To add custom column in sales invoice/order grid. You have to perform following 3 steps.

Step 1. Add column to sales_order_grid/sales_invoice_grid database table

Columns are added to database tables using InstallSchema script. This script should be updated in the same module, where custom_column field was added.

We need to create InstallSchema at following directory :

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

It will create a column named custom_column, of type text, with comment “Custom Column” to  sales_invoice and sales_invoice_grid during installation. If you want to add custom column for sales order grid then you can use sales_order and sales_order_grid table instead of sales_invoice and sales_invoice_grid.

sales_order_grid and sales_invoice_grid  is index table and is used for order/invoice grid rendering speed up. It is designed to store all information required for sales order/invoice grid rendering, so custom columns are required to be added to this table.

 To reflect changes in database in Magento 2, Optionally deleting module entry from setup_module table and running bin/magento setup:upgrade command will work for you.
After this step you can see that custom_column column is present in sales_order_grid/sales_invoice_grid(as well as sales_order and sales_invoice) table, but is remaining empty as it is not mapped to any data source. Let’s do it in second step.

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

First let’s understand how sales_order_grid/sales_invoice_table table is populated.

For order When order is placed and for invoice when invoice is created, data related to this order/invoice is selected from sales_order/sales_invoice table joining several additional tables and inserted to sales_order_grid/sales_invoice_grid table. This operation is initiated by \Magento\Sales\Model\ResourceModel\Grid::refresh function and the default select is declared in MAGENTO_SALES_MODULE/etc/di.xml” file.

So to include our custom column in mentioned insert from select, we have to extend di configuration creating app/code/VENDOR/MODULE/etc/adminhtml/di.xml file.

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>

The above code is for custom column mapping for invoice grid. To add mapping for order grid. you just need to replace bold text with Magento\Sales\Model\ResourceModel\Order\Grid and sales_order

This above configuration is specifying that custom_column  is added to select from sales_order/sales_invoice  and will populate sales_order_grid.custom_column/sales_invoice_grid.custom_column column with corresponding value from sales_order.custom_column/sales_invoice.custom_column.

After this step, our custom_column column in sales_order_grid table is populated with value from sales_order table each time order is placed and for invoices, custom coulmn in sales_invoice_grid is populated with value from sales_invoice table each time invoice is created.  Still, column will exist only in database, and will not be visible in admin panel.

Step 3. Configure UI grid component to display custom column

To reflect this custom column on admin panel sales order/sales invoice grid, we have to extend sales_order_grid/sales_order_invoice_grid UI component by adding a UI configuration file in our module.

It is possible to extend UI configuration for sales order and sales invoice grid introducing app/code/VENDOR/MODULE/view/adminhtml/ui_component/sales_order_grid.xml and app/code/VENDOR/MODULE/view/adminhtml/ui_component/sales_order_invoice_grid.xml file.

Generally, it should have the same name and path in relation to module directory as main sales order and invoice grid UI component file.

Add the following xml snippet to the ui configuration file

<!-- Add the column "custom_column" (Custom Column) to the sales invoice grid. -->
<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>

Above code is for adding custom_column column to sales invoice grid. to add custom column for sales order grid replace sales_order_invoice_columns with sales_order_columns

This will extend sales_order_columns/sales_order_invoice_columns and add a column based on custom_column field, of type text, with translatable label “Custom Column”.

Don’t forget to refresh config cache.

Hope this tutorial was useful for you. If you have any query or feedback, leave it in the comment segment, I will response as soon as possible:

Check if Customer is Logged in or not in Magento 2

As we all know, there are so many condition where we need to check whether customer is currently logged in or not for specific functionality. In this blog post you will have idea about how to check if customer is logged in or not in Magento 2.

It is always best practice to do not use ObjectManager directly. You can use following code to check user is logged in or not in any class except template files.

You need to use this Magento\Customer\Model\Session::isLoggedIn() method in following way.  First you need to inject the following class in your constructor: /Magento/Customer/Model/Session

protected $_session;

public function __construct(
    ...
    \Magento\Customer\Model\Session $session,
    ...
) {
    ...
    $this->_session = $session;
    ...
}

Then in you class you can use following snippet of code anywhere.

if ($this->_session->isLoggedIn()) {
    // Customer is logged in 
} else {
    // Customer is not logged in
}

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction Magento

SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction. This issue is called Deadlock issue. Some times it is caused on the database.

The reason behind this issue is if you are running a lot of custom scripts and killing the scripts before the database connection gets chance to close.

If you can login to MySQL from CLI and run the following command

SHOW PROCESSLIST;

you will see the following output

+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows_read |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
| 162 | db_user| 111.11.0.65:21532 | db_name| Sleep | 3850 | | NULL | 0 | 0 | 0 |
| 175 | db_user| 111.11.0.65:27488 | db_name| Sleep | 3757 | | NULL | 0 | 0 | 0 |
| 176 | db_user| 111.11.0.65:32670 | db_name| Sleep | 3731 | | NULL | 0 | 0 | 0 |
| 190 | db_user| 111.11.0.65:47424 | db_name | Sleep | 3639 | | NULL | 0 | 0 | 0 |
| 210 | db_user| 111.11.0.65:56029 | db_name| Sleep | 3591 | | NULL | 0 | 0 | 0 |
| 211 | db_user| 111.11.0.65:59201 | db_name| Sleep | 3567 | | NULL | 0 | 0 | 0 |
| 225 | db_user| 111.11.0.65:2390 | db_name| Sleep | 3529 | | NULL | 0 | 0 | 0 |
| 227 | db_user| 111.11.0.65:10125 | db_name | Sleep | 3473 | | NULL | 0 | 0 | 0 |
| 230 | db_user| 111.11.0.65:18407 | db_name| Sleep | 3424 | | NULL | 0 | 0 | 0 |
| 280 | db_user| 111.11.0.65:35679 | db_name| Sleep | 3330 | | NULL | 0 | 0 | 0 |
| 287 | db_user| 111.11.0.65:57815 | db_name| Sleep | 1860 | | NULL | 0 | 0 | 0 |
| 291 | db_user| 111.11.0.67:20650 | db_name| Sleep | 188 | | NULL | 1 | 0 | 0 |
| 325 | db_user| 111.11.0.65:36618 | db_name| Query | 0 | NULL | SHOW PROCESSLIST | 0 | 0 | 0 |
| 330 | db_user| 111.11.0.75:38717 | db_name| Sleep | 0 | | NULL | 0 | 0 | 0 |
| 426 | db_user| 111.11.0.75:38819 | db_name| Sleep | 0 | | NULL | 61 | 61 | 61 |
+———+—————–+——————-+—————–+———+——+——-+——————+———–+—————+———–+
15 rows in set (0.00 sec)

You can see as an above example
162 the command is sleep and time is 3850 . This is preventing other operations.
These sleep command processes should be killed 1 by 1 using the command as following.

KILL 162;

Once you have killed all the sleep connections, things should start working as normal again.

If you have any query in SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction Magento, leave it in the comment segment, I will response as soon as possible

 

“EAV” & “Flat Catalog” in Magento

Difference between EAV catalog and Flat catalog is Magento as following:

1. Eav Catalog

EAV is entity attribute value database model, where data is fully in normalized form. Each column data value is stored in their respective data type table. Example, for a product,

product ID is stored in catalog_product_entity_int table,

product name in catalog_product_entity_varchar table,

product price in catalog_product_entity_decimal table,

product created date in catalog_product_entity_datetime table,

product description in catalog_product_entity_text table.

EAV is complex as it joins 5-6 tables even if you want to get just one product’s details.

Columns are called attributes in EAV.

2. Flat Catalog

Flat model uses just one table, so it’s not normalized and uses more database space. It clears the EAV overhead,

It’s good when comes to performance, as it will only require one query to load whole product instead of joining 5-6 tables to get just one product’s details.

Columns are called fields in flat model.

Magento implemented indexers which will periodically query the standard collections and populate flat database tables in following format. Where * is store id.

catalog_category_flat_store_*
catalog_product_flat_*

These tables have non-normalized product and category data that’s intended to be read only. This allows Magento to fetch category and product data in a single query.


You can enable flat catalog by navigating to System > Configuration > Catalog > Frontend > Use Flat Catalog Category | Use Flat Catalog Product. Set this to yes.

If you are adding new attribute in EAV table for catalog. Then don’t forget to run re-indexing (System > Configuration > Index Management). Re-indexing refreshes your flat catalog tables.

Magento flat catalog setting