Add Bought Products to the PrestaShop Order List in the Back Office

In today’s tutorial we will see how to show bought products in the back office order list, and also how to enable filtering by this parameter.

Introduction

Last year, we saw how to filter by country in the Prestashop order list, which is indeed useful when your customer base is spread around the world. Today, we will add another parameter to the orders table, showing us which products are part of the order, effectively saving us a lot of time in many situations when we only need to know what is to be shipped.

One override will be enough to achieve this result: AdminOrdersController.

Overriding the AdminOrdersController

To start off, create a new file in override/controllers/admin and call it AdminOrderController.php. We need to override the construct method; therefore start by adding the following inside php tags:

class AdminOrdersController extends AdminOrdersControllerCore
{

	public function __construct()
	{

	}
}

If you don’t know anything about overrides, have a look at how to override Prestashop classes and controllers at Prestashop’s Doc.

Now let’s add some base parameter. Have a look at your own original AdminOrdersController file (controllers/admin) if you use a version different from 1.5.6.x.

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();
	}
}

We didn’t do anything special here, just setting up the module’s basics by copying the original method. Time to extend the SELECT statement!

Selecting products within the order query

Right after the object definition we added before, we need to extend the $this->_select variable in order to grab products as well. Here is how it looks in the original order controller:

	$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		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';

So, grab this, add it after the last lines we wrote, and change it to:

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

As you can see, we added: (SELECT GROUP_CONCAT(odd.product_name SEPARATOR “, “) FROM `’._DB_PREFIX_.’order_detail` odd WHERE odd.id_order = a.id_order) as products,. This is the subquery that will grab products from the order_detail table, given that the order id is the current one, of course! Using GROUP_CONCAT will ensure all products will br shown, separated by a comma and space. You can use any separator you prefer, of course.

Our override so far:

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();

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

}

We don’t need extra stuff for the query, so right after the select variable, add:

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
		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.')';
		$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'];

In any casy, copy everything after your original controller’s select variable, down to before $this->fields_list.

Let’s now more to showing products!

Displaying the selected products

To add new fields in the orders table, we need to extend $this->fields_list. Therefore, grab the definition from the original controller, and paste it after the last foreach we added. On 1.5.6.x, it looks like this:

		$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',
			'order_key' => 'osname'
		),
		'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)
		);

Where to add the product list? I chose to add it right after the customer name. We have to add the new field as an array, like this:

		'products' => array(
			'title' => $this->l('Products'),
			'havingFilter' => true,
			'filter_key' => 'products',
			'width' => 370,
		),

In the order, we have the column title (of little importance, it’s the one shown to the viewer); the possibility to filter by those parameters; the name of the filter key (caution! it must be the same as the name of the parameter we grabbed in the select statement!); the column width in pixels.

At this point, add it to the array:

		$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,
		),
		'products' => array(
			'title' => $this->l('Products'),
			'havingFilter' => true,
			'filter_key' => 'products',
			'width' => 370,
		),
		'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',
			'order_key' => 'osname'
		),
		'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)
		);

We are almost done!

Finishing off the override

The rest of the override is basically identical to the original file, so from that one, grab everything you find after the fields list in the construct method, and paste it into the override:


		$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);
		}

		parent::__construct();

Lastly, since we don’t want our new fields list to be overridden by the original one, change parent::__construct(); to AdminController::__construct.

Save, go to cache/ and delete the class_index.php file so that our override can take place. We are done!

The final override


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();

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

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
		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.')';
		$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,
		),
		'products' => array(
			'title' => $this->l('Products'),
			'havingFilter' => true,
			'filter_key' => 'products',
			'width' => 370,
		),
		'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',
			'order_key' => 'osname'
		),
		'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)
		);

		$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);
		}

		AdminController::__construct();
	}
}

Additional Resources

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

Leave a Reply

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


6 − 1 =

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>