To create a support ticket, start a search

Products Grid: Adding a column 'nb of sales for 2017'

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 year, here 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_year_2017

 

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

               Field name: Year_2017

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

 

Here we look for valid orders with a status 4 or 5 (refer to PrestaShop > Orders > Status) for year 2017.

 

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