We want to add a column that shows the number of orders for a product in the last month.
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.