[SOLVED] Adding Columns to Order Table - Customer Note and Last Message

[behna]

Hi,

I just want to share my solution. I needed to have "customer note" and "last message sent" columns visible in the orders list (we will be using them for further internal identification of orders).

Since I haven't found any solution, I figured it out myself.

 

In order not to change core files, we will use override. That will add required functionality w/o affecting the original file. Original file can be found at \controllers\admin\AdminOrdersController.php

 

1. Create file  \override\controllers\admin\AdminOrdersController.php

2. Paste the following code

<?php

class AdminOrdersController extends AdminOrdersControllerCore {
    public fuction __construct() {

        //calls the original core file
        parent::__construct();


        //to access order messages we need to join ps_customer_thread table
        $this->_join .= 'LEFT JOIN ps_customer_thread ct ON ct.id_order=a.id_order';


        //this selects note and creates
        $this->_select .= ', c.note as `note`';

       

        //for each order, we select the last message (max. 1), refering to table customer_thread which we joined above to assign message to order. You can select the first one sent by changing DESC to ASC.

        $this->_select .= ', (SELECT message FROM ps_customer_message cm WHERE cm.id_customer_thread=ct.id_customer_thread ORDER BY cm.date_upd DESC LIMIT 1) AS `message`';


        //adding note to colums (fields_list) - we named it 'note' above
        $this->fields_list['note'] = array(
                'title' => $this->l('Note'),
                'havingFilter' => true,
             );
        

       //similarly, we add message column
        $this->fields_list['message'] = array(
                'title' => $this->l('Message'),
                'havingFilter' => true,
            );

    }
}

3. Delete file \cache\class_index.php, Prestashop re-indexes files and adds the overriden controller we created. Otherwise none of this will work!

4. You can do the same by editing the original AdminOrdersController.php file, but I do not recommend that!

5. I also wanted to remove columns I won't be using, this can be done using the following code (paste it into the same file)

      unset($this->fields_list['new']);
      unset($this->fields_list['payment']);
      unset($this->fields_list['id_pdf']);

Hope this helps!

Edited June 28, 2015 by behna (see edit history)

[jaimeweb]

Can't seem to get this working on 1.5.4

 

any ideas? 

[Pablo_Lopez]

There's a letter missing in this line 

public fuction __construct() {

This is correct: 

public function __construct() {

[jaimeweb]

Hi,

thanks for your reply, that gave me a blank page on the order list view. If i turn on display_errors i get the following: ( i am on 1.5.4.1)

[PrestaShopDatabaseException]

Unknown column 'cm.date_upd' in 'order clause' SELECT SQL_CALC_FOUND_ROWS a.`id_order`,`id_carrier`,`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`, IF((SELECT COUNT(so.id_order) FROM `ps_orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new, c.note as `note`, (SELECT message FROM ps_customer_message cm WHERE cm.id_customer_thread=ct.id_customer_thread ORDER BY cm.date_upd DESC LIMIT 1) AS `message` FROM `ps_orders` a LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`) 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)LEFT JOIN ps_customer_thread ct ON ct.id_order=a.id_order WHERE 1 ORDER BY a.id_order DESC LIMIT 0,50
at line 605 in file classes/db/Db.php

599. 			WebserviceRequest::getInstance()->setError(500, '[SQL Error] '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
600. 		}
601. 		else if (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS'))
602. 		{
603. 			if ($sql)
604. 				throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
605. 			throw new PrestaShopDatabaseException($this->getMsgError());
606. 		}
607. 	}
608. 
609. 	/**
DbCore->displayError - [line 307 - classes/db/Db.php] - [1 Arguments]
DbCore->query - [line 482 - classes/db/Db.php] - [1 Arguments]
DbCore->executeS - [line 2110 - classes/controller/AdminController.php] - [1 Arguments]
AdminControllerCore->getList - [line 1461 - classes/controller/AdminController.php] - [1 Arguments]
AdminControllerCore->renderList - [line 1414 - classes/controller/AdminController.php] - [0 Argument]
AdminControllerCore->initContent - [line 167 - classes/controller/Controller.php] - [0 Argument]
ControllerCore->run - [line 348 - classes/Dispatcher.php] - [0 Argument]
DispatcherCore->dispatch - [line 50 - controlpanel/index.php] - [0 Argument]

Edited January 8, 2016 by jaimeweb (see edit history)