Grille Produits : Ajouter une colonne "nombre de commandes depuis 1 mois"

We want to add a column that shows the number of orders for a product in the last month.

• use filters in the grids to find the most sold products and eventually delist 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 the products panel, click on the in the 'Add a field' panel and enter the following information:

What is the field ID?: nb_order_with_this_product_1_month

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

Field name: Nb orders for the last month
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 count(DISTINCT o.id_order) AS nb_order_with_this_product_1_month

FROM `'._DB_PREFIX_.'order_detail` od

INNER JOIN `'._DB_PREFIX_.'orders` o ON (od.id_order = o.id_order)

WHERE od.product_id = p.id_product

AND o.valid=1

AND o.current_state IN (2,3,4,5,9,17,23,24,25,38)

AND o.date_add >= (SELECT DATE_ADD("'.date("Y-m-d").' 00:00:00", INTERVAL -1 MONTH))

LIMIT 1),0) AS nb_order_with_this_product_1_month';

Here we look for valid orders with a status 4 or 5 (refer to PrestaShop > Orders > Status) with a date of at least one month. You then duplicate this field and modify the line before last with INTERVAL -3MONTH)) to get the number of sales for the past 3 months.

Click Save on the toolbar

Exit the editing window.

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