How To Add A State Column In The Customers->Addresses Overview

[SLiCK_303]

Hi all,

I would like to know how I can have the state (Florida, California, etc.) of the customers addresses show in the customers->addresses overview page, so I can filter on state.

Anyone have an idea?

[vzex]

Well that's interesting, especially if we wanted to contact regional people say for a craft show or event!

[moy2010]

The fields list is in controllers/admin/AdminAddressesController.php

    $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'),
        'city' => array('title' => $this->l('City')),
        'country' => array('title' => $this->l('Country'), 'type' => 'select', 'list' => $this->countries_array, 'filter_key' => 'cl!id_country'));

You can modify it to include the state, I guess.

[SLiCK_303]

yeah, I figured that one, thank you, is there anyplace else I need to edit?

[SLiCK_303]

I see that's the only file I need to modify, however I need to change the states id into it's name...... Any thoughts on how I do that?

[SLiCK_303]

I think I need to do something like this, like they did for the country, except for a state, to pullout the states name via it's id... $this->_select = 'cl.`name` as country'; $this->_join = ' LEFT JOIN `'._DB_PREFIX_.'country_lang` cl ON (cl.`id_country` = a.`id_country` AND cl.`id_lang` = '.(int) $this->context->language->id.') LEFT JOIN `'._DB_PREFIX_.'customer` c ON a.id_customer = c.id_customer '; $this->_where = 'AND a.id_customer != 0 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c'); $this->_use_found_rows = false; any thoughts?

[moy2010]

I have never done an SQL join using prestashop helpers, but it might work like this:

  $this->_select = 'st.`name` as state';
    $this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'state` st ON (st.`id_state` = a.`id_state`)
        LEFT JOIN `'._DB_PREFIX_.'customer` c ON a.id_customer = c.id_customer
    ';
    $this->_where = 'AND a.id_customer != 0 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c');
    $this->_use_found_rows = false;

[SLiCK_303]

I tried this, and it didnt work, it broke the country...

``` $this->select = 'cl.name as country'; $this->join = ' LEFT JOIN '._DB_PREFIX_.'country_lang cl ON (cl.id_country = a.id_country AND cl.id_lang = '.(int) $this->context->language->id.') LEFT JOIN '._DB_PREFIX_.'customer c ON a.idcustomer = c.idcustomer '; $this->where = 'AND a.idcustomer != 0 '.Shop::addSqlRestriction(Shop::SHARECUSTOMER, 'c'); $this->usefoundrows = false;

    $this->_select = 'st.`name` as state';
    $this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'state` st ON (st.`id_state` = a.`id_state`)
        LEFT JOIN `'._DB_PREFIX_.'customer` c ON a.id_customer = c.id_customer
    ';
    $this->_where = 'AND a.id_customer != 0 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c');
    $this->_use_found_rows = false;

```

[moy2010]

That's really cool, @MDekker.

BTW, I loved the icon, lol.

[SLiCK_303]

Thanx for that, it works like a champ! :thumbsup: I would rather do it with an override however, that way I can change the order of things as well...

[SLiCK_303]

I got the override working....... Thanx for the module tho! You da man

[moy2010]

@SLiCK_303 , can you post the override instructions in case that any other user needs the same functionality?

[SLiCK_303]

I'm not a coder, so I don't know if the way I did it was the correct way, but sure....

Copy the file, /controllers/admin/AdminAddressesController.php, to /override/controllers/admin/ How edit that file, replace lines 75-98 with the following code.. ``` $countries = Country::getCountries($this->context->language->id); foreach ($countries as $country) { $this->countriesarray[$country['idcountry']] = $country['name']; }

    $states = State::getStates($this->context->language->id);
    foreach ($states as $state) {
        $this->states_array[$state['id_state']] = $state['name'];
    }

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

    parent::__construct();
    $this->_select = 'cl.`name` as country, st.`name` as state';
    $this->_join = '
        LEFT JOIN `'._DB_PREFIX_.'country_lang` cl ON (cl.`id_country` = a.`id_country` AND cl.`id_lang` = '.(int) $this->context->language->id.')
        LEFT JOIN `'._DB_PREFIX_.'state` st ON (st.`id_state` = a.`id_state`)
        LEFT JOIN `'._DB_PREFIX_.'customer` c ON a.id_customer = c.id_customer
    ';
    $this->_where = 'AND a.id_customer != 0 '.Shop::addSqlRestriction(Shop::SHARE_CUSTOMER, 'c');
    $this->_use_found_rows = false;

``` Here is my override file in it's entirety... 01510089968403AdminAddressesController.php

The way it is, I could setup the state as a dropdown list like country is, but I like it better typing something in.

[SLiCK_303]

Now, if I could only figure out how to do the same at the bottom of the customers view. At the bottom of viewing a customer is a frame with all their addresses, I wanna find out how to add it there as well. Any ideas?

[SLiCK_303]

I figured it out. I had to edit a core file.. /themes/default/template/controllers/customers/helpers/view/view.tpl. Is there a way to make this an override, instead of editing a core file?

[yaniv14]

try placing it in override/controller/admin/templates/customers/helpers/view/view.tpl

[SLiCK_303]

you da man, that worked, thank you! Here's my override, view.tpl file if anyone cares.... 01510153050686view.tpl

[vzex]

"Let me just make one real quick." :) Envious of those skills @mdekker

@mdekker said in How To Add A State Column In The Customers->Addresses Overview:

Ohh that's tricky, but I recently learnt a quick way to add it via a module. Let me just make one real quick.