Display customer "customer messages" indicator on "Orders" tab

[Rhapsody]

Using PS 1.5.4.2 - in the BO Orders tab, I would like to display an indicator similar to the "New" indicator in the list of orders. This indicator would display if there are any non-private messages associated with the order (e.g. I don't want to see the PayPal status, only if the customer or employee posts a non-private message associated with that order).

 

I know this can be done using an override for AdminOrdersController.php

The override would add the extra field in the list similar to this code that would be inserted just after the 'New' field:

 'message' => array(
  'title' => $this->l('Message'),
  'width' => 25,
  'align' => 'center',
  'type' => 'bool',
  'tmpTableFilter' => true,
  'icon' => array(
0 => 'blank.gif',
1 => array(
 'src' => 'note.png',
 'alt' => $this->l('Customer message'),
)
  ),
  'orderby' => false
 ),

 

The other part of the code would be a sql query located in this code that returns the message variable used for the indicator... this is where I need the help.

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

 

Can anyone help with the code for that query?

Edited August 5, 2013 by Rhapsody (see edit history)

[Dampers]

Hi Rhapsody,

 

Replace the original code by this code on line 91 until 114 (getMessagesByOrderId string) in Message.php

 

public static function getMessagesByOrderId($id_order, $private = false, Context $context = null)
{
  if (!Validate::isBool($private))
	die(Tools::displayError());
 if (!$context)
  $context = Context::getContext();
 $m = Db::getInstance()->executeS('
  SELECT m.*, c.`firstname` AS cfirstname, c.`lastname` AS clastname, e.`firstname` AS efirstname, e.`lastname` AS elastname,
  (COUNT(mr.id_message) = 0 AND m.id_customer != 0) AS is_new_for_me
  FROM `'._DB_PREFIX_.'message` m
  LEFT JOIN `'._DB_PREFIX_.'customer` c ON m.`id_customer` = c.`id_customer`
  LEFT JOIN `'._DB_PREFIX_.'message_readed` mr
	ON mr.`id_message` = m.`id_message`
	AND mr.`id_employee` = '.(isset($context->employee) ? (int)$context->employee->id : '\'\'').'
  LEFT OUTER JOIN `'._DB_PREFIX_.'employee` e ON e.`id_employee` = m.`id_employee`
  WHERE m.`id_order` = '.(int)$id_order.'
  '.(!$private ? ' AND m.`private` = 0' : '').'
  GROUP BY m.id_message
  ORDER BY m.date_add DESC
 ');

 $o = Db::getInstance()->executeS('
  SELECT ct.*, m.*, e.`firstname` AS efirstname, e.`lastname` AS elastname
  FROM `'._DB_PREFIX_.'customer_thread` ct
  LEFT JOIN `'._DB_PREFIX_.'customer_message` m ON m.`id_customer_thread` = ct.`id_customer_thread`
  LEFT OUTER JOIN `'._DB_PREFIX_.'employee` e ON e.`id_employee` = m.`id_employee`
  WHERE ct.`id_order` = '.(int)$id_order.'
  ORDER BY ct.`date_add` DESC'
 );

 return array_merge($o,$m);

}

Edited August 27, 2013 by Dampers (see edit history)

[Rhapsody]

Hi Rhapsody,

 

Replace the original code by this code on line 91 until 114 (getMessagesByOrderId string) in Message.php

 

 

Dampers - I have similar code in a Message.php override attached that inserts the messages in the BO order page as they were in PS 1.4. It was in this post.

 

What I am looking for is how to display a message indicator in the order list (BO Menu, Orders) similar to the "New" indicator on the list.

Message.php

Edited August 28, 2013 by Rhapsody (see edit history)

[shroom]

Any chance for a quick fix regarding the encoding issue?

Accents aren't being displayed properly with this solution

[Octan]

I what to do the same on prestashop 1.7. Does anyone has any idea how?