[v1.6.1.5] [SOLVED] Add company name column in BO search results page

[Nandos]

Hi guys o/

I recently enabled the b2b mode on PS to work with companies and it's working fine so far. Anyway, to improve my user experience, it would be nice to have the possibility to see the company name associated to the user directly in the back office search results page. 

My current option i have is to open the order tab, look in the page and if there isn't, look for it in the appropriate column and filter the results. It works, but it need multiple steps in order to do that. If i had the possibility to rapidly check by company name directly through the serch bar, it would need less steps to do the same. For example: "superman inc." company orders a new suit for his hero and it calls to ask for information. In that case i should search "superman inc." in the search bar, finding the user associated with that company name (with the company name in a specific columns), clicking on it and checking under orders info => 2 seconds and i can give information to my special customer.

Does anyone know how to do that? I'd really appreciate an help. Thanks in advance guys!

Edited September 30, 2016 by Nandos (see edit history)

[Apium]

So, effectively you would like to have an extra column that shows the company name in the orders table?

[Nandos]

So, effectively you would like to have an extra column that shows the company name in the orders table?

No, i already have that. Infact, i enabled it through the activation of b2b mode. I also want to show the extra column with company name in the search results page (you know, when you search for something into the searchbar of back office).

Edited September 28, 2016 by Nandos (see edit history)

[Apium]

Ahh, thanks for the clarification!

 

This is exceptionally easy to built-in. Go to /classes/Customer.php. Then find the method searchByName (search in the file).

 

It should look like this:

    public static function searchByName($query, $limit = null)
    {
        $sql_base = 'SELECT *
				FROM `'._DB_PREFIX_.'customer`';
        $sql = '('.$sql_base.' WHERE `email` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `id_customer` = '.(int)$query.' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `lastname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `firstname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';

        if ($limit) {
            $sql .= ' LIMIT 0, '.(int)$limit;
        }

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
    }

Add another SQL UNION statement, to search in the company field, like so:

    public static function searchByName($query, $limit = null)
    {
        $sql_base = 'SELECT *
				FROM `'._DB_PREFIX_.'customer`';
        $sql = '('.$sql_base.' WHERE `email` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `id_customer` = '.(int)$query.' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `lastname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `firstname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `company` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';

        if ($limit) {
            $sql .= ' LIMIT 0, '.(int)$limit;
        }

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
    }

This will not give you the easthetically pleasing column 'company' in your search results, but it will return customers where the company matches the associated company name. Be sure to give it a try!

 

 

I hope this helps.

Edited September 28, 2016 by Apium (see edit history)

[Nandos]

Ahh, thanks for the clarification!

 

This is exceptionally easy to built-in. Go to /classes/Customer.php. Then find the method searchByName (search in the file).

 

It should look like this:

    public static function searchByName($query, $limit = null)
    {
        $sql_base = 'SELECT *
				FROM `'._DB_PREFIX_.'customer`';
        $sql = '('.$sql_base.' WHERE `email` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `id_customer` = '.(int)$query.' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `lastname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `firstname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';

        if ($limit) {
            $sql .= ' LIMIT 0, '.(int)$limit;
        }

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
    }

Add another SQL UNION statement, to search in the company field, like so:

    public static function searchByName($query, $limit = null)
    {
        $sql_base = 'SELECT *
				FROM `'._DB_PREFIX_.'customer`';
        $sql = '('.$sql_base.' WHERE `email` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `id_customer` = '.(int)$query.' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `lastname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `firstname` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';
        $sql .= ' UNION ('.$sql_base.' WHERE `company` LIKE \'%'.pSQL($query).'%\' '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER).')';

        if ($limit) {
            $sql .= ' LIMIT 0, '.(int)$limit;
        }

        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
    }

This will not give you the easthetically pleasing column 'company' in your search results, but it will return customers where the company matches the associated company name. Be sure to give it a try!

 

 

I hope this helps.

Ok, it works like a charm, thanks a lot

Yeah, like you said, it shows the customer associated with that company name i want to search, but indeed it doesn't show it clearly in the search result. Well, it's a start. If you have any idea how to show it too, just to ensure the co-worker about the functioning of the new feature, it would be just perfect.

Thanks again Apium.

[Apium]

Ahh, yes! Sorry about that, but if you insist and while I'm on it..

 

In /controllers/admin/AdminSearchController.php, you'll find the initCustomerList() function.

 

It should look like this:

    protected function initCustomerList()
    {
        $genders_icon = array('default' => 'unknown.gif');
        $genders = array(0 => $this->l('?'));
        foreach (Gender::getGenders() as $gender) {
            /** @var Gender $gender */
            $genders_icon[$gender->id] = '../genders/'.(int)$gender->id.'.jpg';
            $genders[$gender->id] = $gender->name;
        }
        $this->fields_list['customers'] = (array(
            'id_customer' => array('title' => $this->l('ID'), 'align' => 'center', 'width' => 25),
            'id_gender' => array('title' => $this->l('Social title'), 'align' => 'center', 'icon' => $genders_icon, 'list' => $genders, 'width' => 25),
            'firstname' => array('title' => $this->l('First Name'), 'align' => 'left', 'width' => 150),
            'lastname' => array('title' => $this->l('Name'), 'align' => 'left', 'width' => 'auto'),
            'email' => array('title' => $this->l('Email address'), 'align' => 'left', 'width' => 250),
            'birthday' => array('title' => $this->l('Birth date'), 'align' => 'center', 'type' => 'date', 'width' => 75),
            'date_add' => array('title' => $this->l('Registration date'), 'align' => 'center', 'type' => 'date', 'width' => 75),
            'orders' => array('title' => $this->l('Orders'), 'align' => 'center', 'width' => 50),
            'active' => array('title' => $this->l('Enabled'), 'align' => 'center', 'active' => 'status', 'type' => 'bool', 'width' => 25),
        ));
    }

Simply add 'company', like so:

    protected function initCustomerList()
    {
        $genders_icon = array('default' => 'unknown.gif');
        $genders = array(0 => $this->l('?'));
        foreach (Gender::getGenders() as $gender) {
            /** @var Gender $gender */
            $genders_icon[$gender->id] = '../genders/'.(int)$gender->id.'.jpg';
            $genders[$gender->id] = $gender->name;
        }
        $this->fields_list['customers'] = (array(
            'id_customer' => array('title' => $this->l('ID'), 'align' => 'center', 'width' => 25),
            'id_gender' => array('title' => $this->l('Social title'), 'align' => 'center', 'icon' => $genders_icon, 'list' => $genders, 'width' => 25),
            'firstname' => array('title' => $this->l('First Name'), 'align' => 'left', 'width' => 150),
            'lastname' => array('title' => $this->l('Name'), 'align' => 'left', 'width' => 'auto'),
            'email' => array('title' => $this->l('Email address'), 'align' => 'left', 'width' => 250),
            'company' => array('title' => $this->l('Company'), 'align' => 'left', 'width' => 150),
            'birthday' => array('title' => $this->l('Birth date'), 'align' => 'center', 'type' => 'date', 'width' => 75),
            'date_add' => array('title' => $this->l('Registration date'), 'align' => 'center', 'type' => 'date', 'width' => 75),
            'orders' => array('title' => $this->l('Orders'), 'align' => 'center', 'width' => 50),
            'active' => array('title' => $this->l('Enabled'), 'align' => 'center', 'active' => 'status', 'type' => 'bool', 'width' => 25),
        ));
    }

That should do it!

Edited September 28, 2016 by Apium (see edit history)

[Nandos]

Brilliant!

This was literally driving me crazy. I was looking everywhere except there! Indeed it was easier than i thought.

It works, thanks a lot again, I owe you a beer!

Edit: i guess your code should be implemented as default if b2b mode is active in the next release of PS, don't you think?

Edited September 28, 2016 by Nandos (see edit history)

[Apium]

Would be a good idea, yes! I might make a commit on PS' Github .

 

Anyway, I'm glad this helped you! Feel free to send a Peroni my way!

 

Edit: PR is underway: https://github.com/PrestaShop/PrestaShop/pull/6515

Edited September 28, 2016 by Apium (see edit history)

[Apium]

Okay, the PR has been merged. Somewhere in 1.7, this should be fixed by default!

[Nandos]

Okay, the PR has been merged. Somewhere in 1.7, this should be fixed by default!

Great!