Show Customer group on orders page in backoffice

[NeilD]

Hi,

 

I need to be able to tell which groups a customer belongs to when they make an order on the site.

What is the best way to achieve this?

Can I display the customer groups on the order page?

 

Thanks

Neil

[vekia]

hello

 

you want to display it on orders list on in order specification page?

[NeilD]

Orders list page would be perfect that way I can export them to CSV but either would be good

 

Thanks

[vekia]

in this case you have to modify AdminOrdersController.php file (/controllers/admin/AdminOrdersController.php)

you've got there code:

$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';

		$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'; 

change it to:

	$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`, \' (\', gr.`name`, \')\') 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';

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`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.')';
		$this->_orderBy = 'id_order';
		$this->_orderWay = 'DESC';

effect:

 

 

J. DOE (customer) - where customer is a group name

[NeilD]

Fantastic! Thanks very much, Vekia, that's perfect

[killerkhan]

Hello,

 

How do i get the state of the customer from the address. With the above reference i am able to get the city of the customer but not able to get the state.

 

Anyone can help me please.

 

Thanks,

[vekia]

Hello,

 

How do i get the state of the customer from the address. With the above reference i am able to get the city of the customer but not able to get the state.

 

Anyone can help me please.

 

Thanks,

 

and you want to display it as a separate row of table, or as i added customer group above?

[killerkhan]

Hello Vekia,

 

As you added the customer group that is also fine.

 

 

Thanks in Advance.

[vekia]

one last question because i've noticed something while dealing with code.

customer can select two addresses, one for delivery address, second for invoice

from which one you want to display state?

[killerkhan]

one last question because i've noticed something while dealing with code.

customer can select two addresses, one for delivery address, second for invoice

from which one you want to display state?

 

Hello Vekia,

 

I would like to display the state from the delivery address.

 

Thanks,

[vekia]

okay so you ahve to add these two left joins

        LEFT JOIN `'._DB_PREFIX_.'address` ca ON (a.`id_address_delivery` = ca.`id_address`)
        LEFT JOIN `'._DB_PREFIX_.'state` css ON (ca.`id_state` = css.`id_state`)

then you will be able to use css.name to display state name

[cocodeluxe]

in this case you have to modify AdminOrdersController.php file (/controllers/admin/AdminOrdersController.php)

you've got there code:

$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';

		$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'; 

change it to:

	$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`, \' (\', gr.`name`, \')\') 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';

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`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.')';
		$this->_orderBy = 'id_order';
		$this->_orderWay = 'DESC';

effect:

 

 

J. DOE (customer) - where customer is a group name

 

Please this trick for 1.6.0.6

[cocodeluxe]

Please Vekia, the trick not works in 1.6.0.6

 

thanks

[vekia]

don't copy whole code, just lines that i added

[cocodeluxe]

don't copy whole code, just lines that i added

Yes I add only this line and nothing change

 LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`id_lang`= '.(int)$this->context->language->id.')

[cocodeluxe]

 

Yes I add only this line and nothing change

 LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`id_lang`= '.(int)$this->context->language->id.')

I  bit of help please when you can

 

thanks

[killerkhan]

okay so you ahve to add these two left joins

        LEFT JOIN `'._DB_PREFIX_.'address` ca ON (a.`id_address_delivery` = ca.`id_address`)
        LEFT JOIN `'._DB_PREFIX_.'state` css ON (ca.`id_state` = css.`id_state`)

then you will be able to use css.name to display state name

 

Hello Vekia,

 

It worked like a charm.

 

Thanks,

[vekia]

 

Yes I add only this line and nothing change

 LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`id_lang`= '.(int)$this->context->language->id.')

you have to change also SELECT condition

[cocodeluxe]

you have to change also SELECT condition

Where and how please

 

thanks

[cocodeluxe]

Where and how please

 

thanks

ok thanks 

 

solved

[vekia]

just copy SELECT clause that i proposed to use

[cocodeluxe]

just copy SELECT clause that i proposed to use

solved

 

very thanks

[FJakub]

Hello, 

Is it possible to display the list of customers in what is classified a group? 

I have a prestashop 1.5.6.2 

 

thank you very much

[cocodeluxe]

posibility to show the group´s name only when not it´s  customer ?

 

 

thanks

[leonetrek]

Hello. How can I show the name of the group user on all pages near the price? And in the heder near basket?

 

Thank`s

 

Prestashop 1.5.4.1

Edited June 27, 2014 by leonetrek (see edit history)

[vekia]

hello

you mean that you want to display group that customer is associated with?

[leonetrek]

hello

you mean that you want to display group that customer is associated with?

Yes. Thank that have responded. I have already solved this problem.

On my 1.5.4.1 It works

 

Here's a link to solve

http://www.prestashop.com/forums/topic/228277-get-customer-group-id-in-smarty/

[vekia]

 

Yes. Thank that have responded. I have already solved this problem.

On my 1.5.4.1 It works

 

Here's a link to solve

http://www.prestashop.com/forums/topic/228277-get-customer-group-id-in-smarty/

 

great

thank you for sharing url to solution :-)

[djuhari]

Could you help me to get name of customer group on order detail page (BO)

I now only get the id number in order detail page by editing the veiw.tpl file. (using 1.5.6) 

[vekia]

hello

have you tried method with controller modification that i pasted several posts before/

[djuhari]

Which post. I mean not in order list. But in order detail in bo. I manage it by modifying the controller. But don't know, if the code is beautiful. It works. Copy the code from customer controller.

[bocane]

Hi,

 

Adding the shipping chosen by the client in the order list would be a great feature (for example in the same column as the payment method).

 

Thank you!

[cocodeluxe]

Please which it´s the Concat query for  customer´s complete name and customer group

[killerkhan]

in this case you have to modify AdminOrdersController.php file (/controllers/admin/AdminOrdersController.php)

you've got there code:

$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';

		$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'; 

change it to:

	$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`, \' (\', gr.`name`, \')\') 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';

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`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.')';
		$this->_orderBy = 'id_order';
		$this->_orderWay = 'DESC';

effect:

 

 

J. DOE (customer) - where customer is a group name

 

 

 

Hello Vekia,

 

How can i add state in the Customers->Addresses.

 

 

 

Thanks,

[rjtrabajo]

Hi,

 

i have 1.6.0.14 and i added the line: 

 

LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`id_lang`= '.(int)$this->context->language->id.')

 

but nothing happen, can you tell me what more i have to make?

 

many thanks for your help.

[rjtrabajo]

ok i solved! 

 

but i have a question, this shows the group where is the customer. 

 

i have  a modification where customers can change the group, and for example if they buy with GROUP A, in back office show (Group A), but if after they change to GROUP B and make another order, change all orders before too in back office to GROUP B.

 

thanks

[vekia]

in this case it will be necessary to store somewhere information what group was used to order stuff from shop.

there is no other way.

if i were you i will add new column to ps_orders table "customer_group" and i will add there this information

then you will be able to display it easily

[wasyl_sz]

Please Help me, how can i add state in the Customers->Addresses?

[pxloft]

Hi,

I am trying to add the customers company in the The same way you did with the customer group... how do I do this?

[RSI-SHOP]

Great code work !

 

Vekia I have question .

 

Possible is display name group in separate column  ?

[CoolSeed]

Hi!

 

I have the same question basically.

Display the group name in the customers listing in the backoffice.

So that my client when viewing his customers knows which group clients are into.

 

I can't manage to make my AdminCustomersController override works. I have found this one but it show only the id. I try to fiddle with your code here but no luck.

 

_______

 

class AdminCustomersController extends AdminCustomersControllerCore

{

  public function __construct()

 

  {

   parent::__construct();

   $this->_select .= ', ca.id_group as group_id';

   $this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'customer_group` ca ON (ca.`id_customer` = a.`id_customer`)';

   $this->fields_list['group_id'] = array(

   'title' => $this->l('Groupe'),

   'width' => 200,

   'callback' => 'groupe'

   );

  }

   public function groupe($id_group)

  {

   return $id_group;

  }

}

 

_________

 

If someone knows how to display in a separate column the name of the group on the BO customers list.

 

Best regards,

 

Quentin

[planetediscount]

Hello Coolseed, have you arrive to get the name of group, instead of ID ?

 

By the way your code working fine on 1.6.1.4

[CoolSeed]

Hello,

 

No I did not manage to do it. I gave up, needed to move to other issues, and forgot a bit about it 

But if someone has the solution, I will be happy to give it a try.

[deffi]

So we did those changes in code and we see customers group and this is pretty cool but if we could sort customers by group like we sorting them by status it will be super helpful to manage orders. Is there any solutions or thoughts ?

[beepow]

On 5/9/2014 at 2:22 PM, vekia said:

just copy SELECT clause that i proposed to use

How can I Override this instead of changing the original file?

(thank you in any case)

[antonio_fiorella]

 

 

On 10/15/2013 at 8:00 PM, vekia said:

in this case you have to modify AdminOrdersController.php file (/controllers/admin/AdminOrdersController.php)

you've got there code:

$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';

		$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'; 

change it to:

	$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`, \' (\', gr.`name`, \')\') 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';

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`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.')';
		$this->_orderBy = 'id_order';
		$this->_orderWay = 'DESC';

effect:

 

 

J. DOE (customer) - where customer is a group name

 

Edited October 21, 2020 by antonio_fiorella (see edit history)

[tattichemarketing]

Ciao

facendo la modifica su prestashop 1.7

il sistema mi da questo errore

Query SQL non valida
Unknown column 'country.id_country' in 'field list'

On 10/15/2013 at 8:00 PM, vekia said:

in this case you have to modify AdminOrdersController.php file (/controllers/admin/AdminOrdersController.php)

you've got there code:

$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';

		$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'; 

change it to:

	$this->_select = '
		a.id_currency,
		a.id_order AS id_pdf,
		CONCAT(LEFT(c.`firstname`, 1), \'. \', c.`lastname`, \' (\', gr.`name`, \')\') 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';

		$this->_join = '
		LEFT JOIN `'._DB_PREFIX_.'customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `'._DB_PREFIX_.'group_lang` gr ON (gr.`id_group` = c.`id_default_group` AND gr.`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.')';
		$this->_orderBy = 'id_order';
		$this->_orderWay = 'DESC';

effect:

 

 

J. DOE (customer) - where customer is a group name

 

Edited October 21, 2020 by tattichemarketing (see edit history)