Products Grid: Adding a column 'nb of sales from dd:mm:yyyy to dd:mm:yyyy'

Target

We would like to add a column in the Combinations panel displaying the number of sales for year 2021.

We will then be able to:

- use filters in the grids to find the most sold products and eventually remove unsold products

- export quickly the products grid including this field to work on an Excel spreadsheet

Setup

To add the field to the list of available fields for your products grids, click on the in the 'Add a field' panel and enter the following information:

What is the field ID?: nb_sales

Sc creates the field, you now need to populate the grid with:

Field name: nb_sales_2021
Table: Another table
Type: just displayed
Refresh combinations: No

From the Advanced Properties panel on the right handside:

- select the menu SQLSelect and enter:

return ',COALESCE((SELECT SUM(od.product_quantity) AS nb
            FROM `'._DB_PREFIX_.'order_detail` od
            INNER JOIN `'._DB_PREFIX_.'orders` o ON (od.id_order = o.id_order)
            WHERE od.product_attribute_id=pa.id_product_attribute
            AND o.valid=1
            AND o.current_state IN (4,5)
            AND o.date_add >= ("2021-01-01 00:00:00")
            AND o.date_add <= ("2021-12-31 00:00:00")
            LIMIT 1),0) AS nb_sales';

- select the menu SQLSelect and enter:

$combArray[$combinaison['id_product_attribute']]['nb_sales'] = $combinaison['nb_sales'];

Here we look for valid orders with a status 4 or 5 (refer to PrestaShop > Orders > Status) for the period 01/01/2021 to 31/12/2021.

Exit the editing window.

The new field is now present in the list of available fields and you can add it to your product grids.