We would like to add a column displaying the total amount validated orders on a specific period of time within the customer interface.
To add the field to the list of available fields for your grids, click on the in the 'Add a field' panel and enter the following information:
What is the field ID?: total_valid_orders_periode
Sc creates the field, you now need to populate the grid with:
Field name: Total Validated orders (period)
Table: Another table
Type: only display
From the Advanced Properties panel on the right handside:
- select the menu SQL Select and enter:
return ' , 0 as total_valid_orders_periode ';
- select the menu PHP Get Row Data and enter:
if($col=="total_valid_orders_periode") { $total_valid_orders_periode_sql = Db::getInstance()->getValue(' SELECT SUM(total_paid) FROM '._DB_PREFIX_.'orders WHERE `id_customer` = '.(int)$gridrow['id_customer'].' AND invoice_date >= "2019-01-01 00:00:00" AND invoice_date <= "2019-12-31 23:59:59" AND valid="1"'); if(!empty($total_valid_orders_periode_sql )) $gridrow[$col] = number_format($total_valid_orders_periode_sql, 2, ".",""); }
Exit the editing window.
The new field is now present in the list of available fields and you can add it to your Customer grids.