Filtering by Country on the Prestashop Order List

It can be useful to be able to see from which country you received an order, without having to inspect each and every of them. In this tutorial, we will see how to add a ‘country’ column to the Prestashop order list, which will also enable us to filter them by country itself.

Download Project Files

  • Version used: Prestashop 1.5.6

Once more, in this tutorial we will make use of overrides. To be specific, we will extend the AdminOrdersController constructor to first grab all the country related data from our database, and then display the new column. To make some room, we will get rid of the invoice column, but feel free to simply add the new one, if you need that, or even replace another which is not useful to you. Let’s get started!

Extending the AdminOrders Controller

As I always want to stress, when adding new functions to Prestashop, it’s fundamental not to touch the core, to avoid losing all modifications at the next upgrade. Therefore, head over to override/controllers/admin and create a new file called AdminOrdersController.php. Go back to Prestashop’s root folder, then cache/ and delete class_index.php to make sure our beloved e-commerce software can read the new override.

Back to the new file, let’s start by adding some preliminary class code inside php tags:

class AdminOrdersController extends AdminOrdersControllerCore
{

	public function __construct()
	{
		AdminController::__construct();
	}
}

Note: As you can see, we are calling the current class’s grandfather, instead of direct parent. Why? If we were to use parent::_construct, the original adminOrdersController data and settings would have overridden again our modifications, which would have never taken place. Thus, we skip the call to the original class constructor and call AdminController::__construct directly.

At this point, if you visit the order list page, you will have nothing but a blank page. Don’t freak out! This is just the beginning.

Grabbing all data we need for the new column

Now, let’s copy a bunch of properties from the original AdminOrdersController:


class AdminOrdersController extends AdminOrdersControllerCore
{

	public function __construct()
	{
		$this->table = 'order';
		$this->className = 'Order';
		$this->lang = false;
		$this->addRowAction('view');
		$this->explicitSelect = true;
		$this->allow_export = true;
		$this->deleted = false;
		$this->context = Context::getContext();

		AdminController::__construct();
	}

}

Make sure the grandpa’s call stays at the very bottom of the constructor! Then, it’s time to tell this class which fields we want to grab. It’s pretty much the same as the default, with a slight addition. Add the following right before the grandfather’s constructor’s call:


		$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		cl.name as country,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`) AS `customer`,
		osl.`name` AS `osname`,
		os.`color`,
		IF((SELECT COUNT(so.id_order) FROM `'._DB_PREFIX_.'orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new';

The only addition here is cl.name as country,. Now, we need to join all tables we need, of course!

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
		LEFT JOIN `'._DB_PREFIX_.'address` ad ON (ad.`id_address` = a.`id_address_delivery`)
		LEFT JOIN `'._DB_PREFIX_.'country_lang` cl ON (cl.`id_country` = ad.`id_country` AND cl.`id_lang` = '.(int)$this->context->language->id.')
		LEFT JOIN `'._DB_PREFIX_.'order_state` os ON (os.`id_order_state` = a.`current_state`)
		LEFT JOIN `'._DB_PREFIX_.'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = '.(int)$this->context->language->id.')';

Compared to the original one, this has two new joins:

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'address` ad ON (ad.`id_address` = a.`id_address_delivery`)
		LEFT JOIN `'._DB_PREFIX_.'country_lang` cl ON (cl.`id_country` = ad.`id_country` AND cl.`id_lang` = '.(int)$this->context->language->id.')

Why do we need two? Because, as the first table we are grabbing data from is ps_orders, whose alias is a, we only have access to the address ID (id_address_delivery). This tells us which address the customer used to checkout. I deliberately chose this one, but you can use id_address_invoice instead, if you prefer. At this point, having the address id, we grab the country associated to this very address, by joining the ps_country_lang table to get the real name of it.

So, the logical ‘table lookup’ to get the country name is ps_orders.id_address_delivery » ps_address.id_address – ps_address.id_country » ps_country_lang.id_country – ps_country_lang.name.

We have all data we need, what’s left at this point is copying a bunch of rows from the original controller, and add the new column to the fields list.

Adding the new column to the fields_list

First things first, right after the last code snippet, add the following, grabbed directly from the original AdminOrdersController:

		$this->_orderBy = 'id_order';
		$this->_orderWay = 'DESC';

		$statuses_array = array();
		$statuses = OrderState::getOrderStates((int)$this->context->language->id);

		foreach ($statuses as $status)
			$statuses_array[$status['id_order_state']] = $status['name'];

		$this->fields_list = array(
		'id_order' => array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'width' => 25
		),
		'reference' => array(
			'title' => $this->l('Reference'),
			'align' => 'center',
			'width' => 65
		),
		'new' => array(
			'title' => $this->l('New'),
			'width' => 25,
			'align' => 'center',
			'type' => 'bool',
			'tmpTableFilter' => true,
			'icon' => array(
				0 => 'blank.gif',
				1 => array(
					'src' => 'note.png',
					'alt' => $this->l('First customer order'),
				)
			),
			'orderby' => false
		),
		'customer' => array(
			'title' => $this->l('Customer'),
			'havingFilter' => true,
		),
		'total_paid_tax_incl' => array(
			'title' => $this->l('Total'),
			'width' => 70,
			'align' => 'right',
			'prefix' => '<b>',
			'suffix' => '</b>',
			'type' => 'price',
			'currency' => true
		),
		'payment' => array(
			'title' => $this->l('Payment: '),
			'width' => 100
		),
		'osname' => array(
			'title' => $this->l('Status'),
			'color' => 'color',
			'width' => 280,
			'type' => 'select',
			'list' => $statuses_array,
			'filter_key' => 'os!id_order_state',
			'filter_type' => 'int'
		),
		'date_add' => array(
			'title' => $this->l('Date'),
			'width' => 130,
			'align' => 'right',
			'type' => 'datetime',
			'filter_key' => 'a!date_add'
		),
		'id_pdf' => array(
			'title' => $this->l('PDF'),
			'width' => 35,
			'align' => 'center',
			'callback' => 'printPDFIcons',
			'orderby' => false,
			'search' => false,
			'remove_onclick' => true)
		);

The important part to notice here is the fields_list assignment. This contains all columns of the order table, and tells to each one how to behave. As mentioned at the beginning of the tutorial, I will get rid of the last field, id_pdf, in order to make some room for countries. Once more, feel free to simply add the new one.

Here is the array that needs to be added:


'country' => array(
			'title' => $this->l('CT'),
			'width' => 60,
			'align' => 'center',
			'filter_key' => 'cl!name')

Explanation: For lists of objects in the back office, Prestashop uses its own helper to build the table. Each of the keys of the fields_list array is the name property of the input box, which will be used to filter this column. Of course, provided that you actually want to filter it. The array values determine properties for the table column, in this case: the text displayed at the top, the column’s width, whether the text needs to be centered, and the name of the key I want to use as filter. As I mentioned a couple of lines above, by default Prestashop uses the array key to look for a value to filter. If we specify a filter_key in the values array, we can alter the lookup to our content. In this case, if somebody enters a value, say ‘Italy’ for the country column, and hits filter, Prestashop will look for ‘Italy’ in cl.name (notice an exclamation mark must be used in place of the dot here). Simply put: the filter_key value must reflect the database field you want to target when filtering. Once more, in this case, the Country Name.

For lists of objects in the back office, Prestashop uses its own helper to build the table.

Going through all the properties of the table helper is beyond the scope of this tutorial, but if you want to dive into it, I suggest you have a look at the various admin controllers Prestashop has, especially the product and category ones. Unfortunately, there is no real documentation about this subject, except for a few lines on using templates for helpers within a controller at doc.prestashop.com.

In any case, here is how the fields_list should look after adding our own dataset:


		$this->fields_list = array(
		'id_order' => array(
			'title' => $this->l('ID'),
			'align' => 'center',
			'width' => 25
		),
		'reference' => array(
			'title' => $this->l('Reference'),
			'align' => 'center',
			'width' => 65
		),
		'new' => array(
			'title' => $this->l('New'),
			'width' => 25,
			'align' => 'center',
			'type' => 'bool',
			'tmpTableFilter' => true,
			'icon' => array(
				0 => 'blank.gif',
				1 => array(
					'src' => 'note.png',
					'alt' => $this->l('First customer order'),
				)
			),
			'orderby' => false
		),
		'customer' => array(
			'title' => $this->l('Customer'),
			'havingFilter' => true,
		),
		'total_paid_tax_incl' => array(
			'title' => $this->l('Total'),
			'width' => 70,
			'align' => 'right',
			'prefix' => '<b>',
			'suffix' => '</b>',
			'type' => 'price',
			'currency' => true
		),
		'payment' => array(
			'title' => $this->l('Payment: '),
			'width' => 100
		),
		'osname' => array(
			'title' => $this->l('Status'),
			'color' => 'color',
			'width' => 280,
			'type' => 'select',
			'list' => $statuses_array,
			'filter_key' => 'os!id_order_state',
			'filter_type' => 'int'
		),
		'date_add' => array(
			'title' => $this->l('Date'),
			'width' => 130,
			'align' => 'right',
			'type' => 'datetime',
			'filter_key' => 'a!date_add'
		),
		'country' => array(
			'title' => $this->l('CT'),
			'width' => 60,
			'align' => 'center',
			'filter_key' => 'cl!name')
		);

We are done, as the only thing left is to copy over a tiny snippet grabbed directly from the original controller, right after the fields_list:

		$this->shopLinkType = 'shop';
		$this->shopShareDatas = Shop::SHARE_ORDER;

		if (Tools::isSubmit('id_order'))
		{
			// Save context (in order to apply cart rule)
			$order = new Order((int)Tools::getValue('id_order'));
			if (!Validate::isLoadedObject($order))
				throw new PrestaShopException('Cannot load Order object');
			$this->context->cart = new Cart($order->id_cart);
			$this->context->customer = new Customer($order->id_customer);
		}

Save, reload the page and try to filter the column. We are done!

Need Prestashop Modules? Have a look at my Prestashop Addons Store!

Looking for quality PrestaShop Web Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , | Posted under PrestaShop | RSS 2.0

Author Spotlight

Fabio Porta

Fabio Porta

Fabio has been involved in web development and design since 2005, when launched his first website at the age of 16. He’s now highly skilled in both client and server side development, along with design, and since August 2012 runs a successful website about PrestaShop tutorials and Prestashop Modules called Nemo’s Post Scriptum, at http://nemops.com

3 Comments on Filtering by Country on the Prestashop Order List

  1. Frank Prabel says:

    Fabio,
    Thank you for a very clear and very useful explanation.
    I have a slightly different requirement. For accounting purposes I need to display each order row twice (with slightly different fields) and then export all the rows to csv through the export button on the Admin Orders page.
    So, would you know how to display each row twice on the orders list?
    Alternatively, if you know where the export orders code is located, I could work to customize the export code. But so far I have not been able to locate it.
    Thanks!
    Frank

    • Fabio Porta Fabio Porta says:

      As each entry has a unique ID, I think it would be quite complex to modify the query to display each one twice. My suggestion in this case, since you also have to export field, would be to use a third party module and export the exact fields you need

  2. Max says:

    Ciao Fabio,
    I’m trying to apply your solution but not to show the country, but the Delivery Address in the Order list.
    Is it possible?
    I’m trying to modify your exampe but without results till now.
    Thanks

    Max

Leave a Reply

Your email address will not be published. Required fields are marked *


9 − 7 =

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>