nanaki nanaki - 2 months ago 41
PHP Question

Prestashop add address column to customer backoffice

I'm using prestashop v 1.6.1.1

What I'm trying to get

I'm trying to add a column called dni to my customer section in backoffice.

What I tried

I know the file to do this is called AdminCustomersController.php and it is located in controllers/admin/AdminCustomersController.php

Also I know that with this query i can get the data in the database:

SELECT ps_address.dni, ps_customer. *
FROM ps_customer
INNER JOIN ps_address ON ps_customer.id_customer = ps_address.id_customer


LIMIT 0 , 30

The current query in the customer section is:

SELECT a.`id_customer`, `firstname`, `lastname`, `email`, a.`active` AS `active`, `newsletter`, `optin` , a.date_add, gl.name as title, ( SELECT SUM(total_paid_real / conversion_rate) FROM ps_orders o WHERE o.id_customer = a.id_customer AND o.id_shop IN (2, 1) AND o.valid = 1 ) as total_spent, ( SELECT c.date_add FROM ps_guest g LEFT JOIN ps_connections c ON c.id_guest = g.id_guest WHERE g.id_customer = a.id_customer ORDER BY c.date_add DESC LIMIT 1 ) as connect, shop.name as shop_name FROM `ps_customer` a LEFT JOIN ps_gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = 1) LEFT JOIN ps_shop shop ON a.id_shop = shop.id_shop WHERE 1 AND a.`deleted` = 0 AND a.id_shop IN (2, 1) ORDER BY `date_add` DESC LIMIT 0, 50


I dont know

I dont know how to change that query to add just ps_address.dni and also get the other columns.

Thanks

EDITED Partially Solved

Well, At the end I didn't change AdminCustomersController.php I have changed AdminAddressesController.php that means change Customer/Directions backoffice.

I have just added this lines and it worked like charm:

$this->fields_list = array(
'id_address' => array('title' => $this->l('ID'), 'align' => 'center', 'class' => 'fixed-width-xs'),
'firstname' => array('title' => $this->l('First Name'), 'filter_key' => 'a!firstname'),
'lastname' => array('title' => $this->l('Last Name'), 'filter_key' => 'a!lastname'),
'address1' => array('title' => $this->l('Address')),
'postcode' => array('title' => $this->l('Zip/Postal Code'), 'align' => 'right'),
'dni' => array('title' => $this->l('DNI'), 'align' => 'right'),
'city' => array('title' => $this->l('City')),
'country' => array('title' => $this->l('Country'), 'type' => 'select', 'list' => $this->countries_array, 'filter_key' => 'cl!id_country'));


Now I have a place to search DNI-customer

Answer

Try this, override the AdminCustomersController, new file in prestashop/override/controllers/admin/ named AdminCustomersController.php

class AdminCustomersController extends AdminCustomersControllerCore {
    public function __construct(){
        $this->bootstrap = true;
        $this->required_database = true;
        $this->required_fields = array('newsletter','optin');
        $this->table = 'customer';
        $this->className = 'Customer';
        $this->lang = false;
        $this->deleted = true;
        $this->explicitSelect = true;

        $this->allow_export = true;

        $this->addRowAction('edit');
        $this->addRowAction('view');
        $this->addRowAction('delete');
        $this->bulk_actions = array(
            'delete' => array(
                'text' => $this->l('Delete selected'),
                'confirm' => $this->l('Delete selected items?'),
                'icon' => 'icon-trash'
            )
        );

        $this->context = Context::getContext();

        $this->default_form_language = $this->context->language->id;

        $titles_array = array();
        $genders = Gender::getGenders($this->context->language->id);
        foreach ($genders as $gender) {
            /** @var Gender $gender */
            $titles_array[$gender->id_gender] = $gender->name;
        }

        $this->_join = 'LEFT JOIN '._DB_PREFIX_.'gender_lang gl ON (a.id_gender = gl.id_gender AND gl.id_lang = '.(int)$this->context->language->id.')
        LEFT JOIN '._DB_PREFIX_.'address addr ON (a.id_customer = addr.id_customer)';
        $this->_use_found_rows = false;
        $this->fields_list = array(
            'id_customer' => array(
                'title' => $this->l('ID'),
                'align' => 'text-center',
                'class' => 'fixed-width-xs'
            ),
            'title' => array(
                'title' => $this->l('Social title'),
                'filter_key' => 'a!id_gender',
                'type' => 'select',
                'list' => $titles_array,
                'filter_type' => 'int',
                'order_key' => 'gl!name'
            ),
            'firstname' => array(
                'title' => $this->l('First name'),
                'filter_key' => 'a!firstname'
            ),
            'lastname' => array(
                'title' => $this->l('Last name'),
                'filter_key' => 'a!lastname'
            ),
            'email' => array(
                'title' => $this->l('Email address')
            ),
            'dni' => array( // Your new field
                'title' => $this->l('DNI'),
                'filter_key' => 'addr!dni'
            )
        );

        if (Configuration::get('PS_B2B_ENABLE')) {
            $this->fields_list = array_merge($this->fields_list, array(
                'company' => array(
                    'title' => $this->l('Company')
                ),
            ));
        }

        $this->fields_list = array_merge($this->fields_list, array(
            'total_spent' => array(
                'title' => $this->l('Sales'),
                'type' => 'price',
                'search' => false,
                'havingFilter' => true,
                'align' => 'text-right',
                'badge_success' => true
            ),
            'active' => array(
                'title' => $this->l('Enabled'),
                'align' => 'text-center',
                'active' => 'status',
                'type' => 'bool',
                'orderby' => false,
                'filter_key' => 'a!active'
            ),
            'newsletter' => array(
                'title' => $this->l('Newsletter'),
                'align' => 'text-center',
                'type' => 'bool',
                'callback' => 'printNewsIcon',
                'orderby' => false
            ),
            'optin' => array(
                'title' => $this->l('Opt-in'),
                'align' => 'text-center',
                'type' => 'bool',
                'callback' => 'printOptinIcon',
                'orderby' => false
            ),
            'date_add' => array(
                'title' => $this->l('Registration'),
                'type' => 'date',
                'align' => 'text-right'
            ),
            'connect' => array(
                'title' => $this->l('Last visit'),
                'type' => 'datetime',
                'search' => false,
                'havingFilter' => true
            )
        ));

        $this->shopLinkType = 'shop';
        $this->shopShareDatas = Shop::SHARE_CUSTOMER;

        AdminController::__construct(); // Important

        $this->_select = '
        addr.dni,
        a.date_add, gl.name as title, (
            SELECT SUM(total_paid_real / conversion_rate)
            FROM '._DB_PREFIX_.'orders o
            WHERE o.id_customer = a.id_customer
            '.Shop::addSqlRestriction(Shop::SHARE_ORDER, 'o').'
            AND o.valid = 1
        ) as total_spent, (
            SELECT c.date_add FROM '._DB_PREFIX_.'guest g
            LEFT JOIN '._DB_PREFIX_.'connections c ON c.id_guest = g.id_guest
            WHERE g.id_customer = a.id_customer
            ORDER BY c.date_add DESC
            LIMIT 1
        ) as connect';

        $this->_group = 'GROUP BY a.id_customer'; // Don't forget this

        // Check if we can add a customer
        if (Shop::isFeatureActive() && (Shop::getContext() == Shop::CONTEXT_ALL || Shop::getContext() == Shop::CONTEXT_GROUP)) {
            $this->can_add_customer = false;
        }

        self::$meaning_status = array(
            'open' => $this->l('Open'),
            'closed' => $this->l('Closed'),
            'pending1' => $this->l('Pending 1'),
            'pending2' => $this->l('Pending 2')
        );
    }
}

You don't have to do anything else. However, if the customer have more of one address you could have unexpected results.

Comments