Products Grid: Adding a column 'nb of products sales in the last month'

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

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

Field name: Nb sales for the last month

Table: enter: special

Refresh combinations: keep 'no'

Type: just displayed

• From the Advanced Properties panel:

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.

• Exit the editing window and add the field to your grids