Prerequisites:
- Store Commander version 2014-03-12
- SC Grids Editor Pro Add-on - version 1.0
Target:
We would like to add a column displaying the number of sales for the last month.
We will then be able to:
- 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 your products grids, click on the in the 'Add a field' panel and enter the following information:
What is the field ID?: nb_sale_1_month
Field name: Nb sales for the last month
Table: enter: special
Refresh combinations: keep 'no'
Type: just displayed
SQLSelectDataSelect :
return ',(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_id = p.id_product
AND o.valid=1
AND o.current_state IN (4,5)
AND o.date_add >= (SELECT DATE_ADD("'.date("Y-m-d").' 00:00:00", INTERVAL -1 MONTH))
LIMIT 1) AS nb_sale_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.