To create a support ticket, start a search

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

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 a specific period, here 01/07/2017 to 31/12/2017

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 add.png in the 'Add a field' panel and enter the following information:

              What is the field ID?: nb_month_2017

 

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

              Field name: july17_dec17

              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 >= ("2017-07-01 00:00:00")
              AND o.date_add <= ("2017-12-31 00:00:00")
              LIMIT 1) AS july17_dec17';

 

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

 

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