Filter Orders by Customer Group in the Backend

[liter2]

I need to filter the backend list of orders by customer groups. I want to make a drop down list in the filter row with the customer groups, when i select a group the list should only show the orders from clients of that specific group.

 

As far as i know i have to extend the $this->field_list an the sql query, but I'm not sure how to do that for the customer groups and what is the best practice to implement this, so that it's future save (updates etc.).

 

Or is there already a option to filter by customer group that i missed?

 

Thanks for your help!

[PhpMadman]

Future safe, not really. But better then editing core file.

 

You have to create an override and override the __construct function.

Then search for if (Country::isCurrentlyUsed('country', true))

The code contained in that if, is what you need to re-create for customer groups.

 

I'm at work at right now, so don't have time to play around with it.

[liter2]

Thank you!

I've made the filter for the customer group, but it's a bit of a hack.

 

That's how my groups look like:

Id | Group
-----------------
3  | Customer
4  | Company 1
5  | Company 2
6  | Company 3

I added this to the query:

$this->_select .= ',country_lang.name as cname';

$this->_join .= 'INNER JOIN `ps_group_lang` group_lang ON (group_lang.`id_group` = (SELECT id_group FROM ps_customer_group g WHERE g.id_customer=a.id_customer ORDER BY g.id_group DESC LIMIT 1) AND group_lang.`id_lang` = '.(int)$this->context->language->id.')';

... and this for the fields_list:

$part1['gname'] = array(
	'title' => 'Customer Group',
	'type' => 'select',
	'list' => $group_array,
	'filter_key' => 'group_lang!id_group',
	'filter_type' => 'int',
	'order_key' => 'gname'
);

In my current case this works well, but the problem is, a customer could be in more than one group, so the filter would fail if I filter for the "Customer" group and the customer is already in the "Company 1" group.

 

Is it possible to define a custom WHERE clause for the filter?

[eraser80]

Thank you!

I've made the filter for the customer group, but it's a bit of a hack.

 

That's how my groups look like:

Id | Group
-----------------
3  | Customer
4  | Company 1
5  | Company 2
6  | Company 3

I added this to the query:

$this->_select .= ',country_lang.name as cname';

$this->_join .= 'INNER JOIN `ps_group_lang` group_lang ON (group_lang.`id_group` = (SELECT id_group FROM ps_customer_group g WHERE g.id_customer=a.id_customer ORDER BY g.id_group DESC LIMIT 1) AND group_lang.`id_lang` = '.(int)$this->context->language->id.')';

... and this for the fields_list:

$part1['gname'] = array(
	'title' => 'Customer Group',
	'type' => 'select',
	'list' => $group_array,
	'filter_key' => 'group_lang!id_group',
	'filter_type' => 'int',
	'order_key' => 'gname'
);

In my current case this works well, but the problem is, a customer could be in more than one group, so the filter would fail if I filter for the "Customer" group and the customer is already in the "Company 1" group.

 

Is it possible to define a custom WHERE clause for the filter?

Hello Liter2,

 

could you please explain me which file/files I have to modify and where? You would save my life and my time  

 

thank you in advance

[David Eschmeyer]

If you dont want to edit your store, you can always do this:  (posted for others seeking an easier solution)

 

use the sql query tool, make this query.

 

SELECT SQL_CALC_FOUND_ROWS 

a.`id_order`,

`reference`,

`total_paid_tax_incl`,

`payment`,

a.date_add as date_add, 

a.id_currency, 

a.id_order AS id_pdf, 

CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,

osl.`name` AS `osname`, 

os.`color`, 

gl.`name`, 

IF((SELECT so.id_order FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new,

country_lang.name as cname, 

IF(a.valid, 1, 0) badge_success FROM `ps_orders` a LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)

INNER JOIN `ps_address` address ON address.id_address = a.id_address_delivery

INNER JOIN `ps_country` country ON address.id_country = country.id_country 

INNER JOIN `ps_customer_group` cgroup ON cgroup.id_customer = c.id_customer 

INNER JOIN `ps_group_lang` gl ON gl.id_group = cgroup.id_group 

INNER JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1) 

LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`) 

LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1)

WHERE 1 ORDER BY a.`id_order` DESC

Edited April 16, 2015 by David Eschmeyer (see edit history)