Add new custom field in customers list - backend - PrestaShop 1.7.

[garciasanchezdani]

Hi all, 

I need to add two new fields in customers listing, in backend, in PrestaShop 1.7.

These two new fields are two new fields created in the table named ps_customer, in particular, optin1 and optin2.

I read official doc, like this: https://devdocs.prestashop.com/1.7/modules/concepts/templating/admin-views/

But I have not it clear at all, for customers listings...

Could you help me?

Thanks in advance.

[garciasanchezdani]

I solved it, thanks to these two posts:

1. https://devdocs.prestashop.com/1.7/development/components/grid/tutorials/modify-grid-in-module/

2. https://webkul.com/blog/adding-a-new-column-in-prestashop-new-symfony-admin-controller-grid-page-with-module/

Summary, thanks to hooks actionCustomerGridDefinitionModifier and actionCustomerGridQueryBuilderModifier

Best regards.

[thehurricane]

On 12/3/2019 at 9:30 AM, garciasanchezdani said:

I solved it, thanks to these two posts:

1. https://devdocs.prestashop.com/1.7/development/components/grid/tutorials/modify-grid-in-module/

2. https://webkul.com/blog/adding-a-new-column-in-prestashop-new-symfony-admin-controller-grid-page-with-module/

Summary, thanks to hooks actionCustomerGridDefinitionModifier and actionCustomerGridQueryBuilderModifier

Best regards.

Hello,

yeah it looks like easy one, but I have one question - where exactly I should put code from Webkuls tutorial? It should be new module? Or put it in some controller?

 

Best regards

[garciasanchezdani]

Hi @thehurricane

I created one hooks module, for add in it these two hooks, in addition of further hooks.

I thought would be the best option.

Best regards,

[wfpaisa]

great

Edited May 11, 2020 by wfpaisa (see edit history)

[apositivo]

and if i update the prestashop version? I can lose the changes by writing code in the CORE?

[garciasanchezdani]

Yes, always try to use overrides or hooks.

[apositivo]

4 hours ago, garciasanchezdani said:

Yes, always try to use overrides or hooks.

thank  you for your response @garciasanchezdani.

but other questions is .. ..

 

we can override this files ?

 

Quote

use PrestaShop\PrestaShop\Core\Grid\Column\Type\DataColumn;
use PrestaShop\PrestaShop\Core\Grid\Filter\Filter;
use Symfony\Component\Form\Extension\Core\Type\TextType;

https://webkul.com/blog/adding-a-new-column-in-prestashop-new-symfony-admin-controller-grid-page-with-module/

[garciasanchezdani]

No, that's the reason what you can make use of hooks mentioned.

I usually create a module named, for example, "custom hooks", where I add hooks what I need.

You can use https://validator.prestashop.com/generator to create the base of your module simply.

Best regads, 

[Vincent Decaux]

If you want to find a full example, you can check my article blog about it and you can find a basic module structure to download: https://applyss.com/2022/03/14/prestashop-show-phone-number-on-customer-listing/

[Ray UK]

@Vincent Decaux Thanks for the example you provided.

I'm having trouble modifying it for my needs.

The SQL in particular I can't get my head around. I have this but it's not working...

		  $searchQueryBuilder->leftJoin(
            'c',
            '`' . pSQL(_DB_PREFIX_) . 'customer`',
            'pa',
            'pa.`id_customer` = c.`id_customer`
                 = (
                    SELECT
                      pa2.`agecheck`
                    FROM
                      `'. pSQL(_DB_PREFIX_) .'customer` pa2
                    WHERE pa2.`id_customer` = pa.`id_customer`
                    ORDER BY LENGTH(pa2.`agecheck`) DESC
                    LIMIT 1
                )'
        );

The field I'm trying to get is in ps_customer and is called agecheck.

Im guessing it might not need the "leftJoin" part ?

Any chance you could point me in the right direction

Thanks

Ray

Edited March 21, 2022 by MerseyRay (see edit history)

[Vincent Decaux]

@MerseyRay

Indeed you won't need to join a new table if the field is already in the same table.

You can just have in your hookActionCustomerGridQueryBuilderModifier function:

$searchQueryBuilder->addSelect( 'c.`agecheck`' );

My left join is useful if you need to get informations from an other table.

[Ray UK]

That simple hey lol.

Thank you, it is now showing and filling in the column.

Is there a list of these hooks available? The next one I'm looking for is the orders list page. It needs to show the same on that page.

Ive tried, in the same module the following code (it does nothing)

		// Create hook in Order List
    public function hookActionOrderGridDefinitionModifier(array $params)
    {
        /** @var GridDefinitionInterface $definition */
        $definition = $params['definition'];

        $definition
            ->getColumns()
            ->addAfter(
                'total',
                (new DataColumn('agecheck'))
                    ->setName($this->l('18?'))
                    ->setOptions([
                        'field' => 'agecheck',
                    ])
		)
			 ->addAfter(
                'agecheck',
                (new DataColumn('agecheckdate'))
                    ->setName($this->l('Check Date'))
                    ->setOptions([
                        'field' => 'agecheckdate',
                    ])
		);

		
        // For search filter
        $definition->getFilters()->add(
            (new Filter('agecheck', TextType::class))
                ->setAssociatedColumn('agecheck')
        );
    }

 

Can multiple hooks be done in this one module ?

The 3rd one, and last one, will be on the customer B.O profile page. This will have the 2 following fields which will be editable

		$searchQueryBuilder->addSelect( 'c.`agecheck`' );
		$searchQueryBuilder->addSelect( 'c.`agecheckdate`' );

 

Edited March 22, 2022 by MerseyRay (see edit history)

[Ray UK]

Ok so I forgot to add the hooks

    public function install()
    {
        return (
            parent::install()
            && $this->registerHook('actionCustomerGridDefinitionModifier')
            && $this->registerHook('actionCustomerGridQueryBuilderModifier')
            && $this->registerHook('actionOrderGridDefinitionModifier')
            && $this->registerHook('actionOrderGridQueryBuilderModifier')
        );
    }

It is now showing the new field, just not filling data yet.

After some thought, I'm thinking this one does now need the leftJoin 😕

So I tried this

// join the customer table to get the pass details
        $searchQueryBuilder->leftJoin(
            'c',
            '`' . pSQL( _DB_PREFIX_ ) . 'customer`',
            'pa',
            'pa.`id_customer` = c.`id_customer`
			AND pa.`id_customer` = (
			SELECT
			pa2.`id_customer`
                FROM
                  `' . pSQL( _DB_PREFIX_ ) . 'customer` pa2
                WHERE pa2.`id_customer` = pa.`id_customer`
                ORDER BY LENGTH(pa2.`agecheck`) DESC
                LIMIT 1
            )'
        );

and get the error "Call to a member function leftJoin() on null"

Edited March 22, 2022 by MerseyRay (see edit history)

[Vincent Decaux]

According to this topic: 

It seems Order uses a different way of hooking. I let you check it, if one of my customer needs, I will do it on my side and post here the correct way to do it.

[Ray UK]

HI @Vincent Decaux,

That seems to be an old post.

I have the fields both appearing in the orders list, and the customer edit page, I just can't get them to fill with data.

It's probably the SQL statement that I cannot get right.

Here is my code now.

<?php

if (!defined('_PS_VERSION_')) {
    exit;
}

use PrestaShop\PrestaShop\Core\Grid\Column\Type\DataColumn;
use PrestaShop\PrestaShop\Core\Grid\Filter\Filter;
use Symfony\Component\Form\Extension\Core\Type\TextType;

class AgeCheck extends Module
{
	/**
     * List of hooks used
     */
    const HOOKS = [
        'actionCustomerGridDefinitionModifier',
        'actionCustomerGridQueryBuilderModifier',
        'actionCustomerFormBuilderModifier',
        'actionOrderGridDefinitionModifier',
        'actionOrderGridQueryBuilderModifier',
    ];
	
    public function __construct()
    {
        $this->name = 'agecheck';
        $this->tab = 'administration';
        $this->version = '1.0.0';
        $this->author = 'Ray Rigby';

        parent::__construct();

        $this->displayName = $this->trans('Age Check for Customer');
        $this->description = $this->trans('Add the extra B.O fields for the Age Verification records');
    }

    public function install()
    {
        return parent::install()
			&& $this->registerHook(static::HOOKS);
    }

    public function uninstall()
    {
        return parent::uninstall();
    }

	// ****************************** CUSTOMER LIST ****************************** 
	
	// Create new column in Customer List
    public function hookActionCustomerGridDefinitionModifier(array $params)
    {
				
        $definition = $params['definition'];

        $definition
            ->getColumns()
            ->addAfter(
                'email',
                (new DataColumn('agecheck'))
                    ->setName($this->l('18?'))
                    ->setOptions([
                        'field' => 'agecheck',
                    ])
		);
		
		/** Remove the following columns */
		$columns = $definition->getColumns();
		$columns->remove('social_title')
			->remove('active')
			->remove('optin')
			->remove('newsletter')
			;
		
		/** Remove the following filters */
		$filters = $definition->getFilters();
		$filters->remove('social_title')
			->remove('active')
			->remove('optin')
			->remove('newsletter')
			;

    }
	
	    /**
     * Add data to the new field in Customer List
     */
    public function hookActionCustomerGridQueryBuilderModifier(array $params)
    {
        $searchQueryBuilder = $params['search_query_builder'];

        $searchCriteria = $params['search_criteria'];
		
		$searchQueryBuilder->addSelect( 'c.`agecheck`' );
    }
	
	
		// ****************************** CUSTOMER PAGE ****************************** 
	
	// Create new column in Customer List
    public function hookActionCustomerFormBuilderModifier(array $params)
    {
    $formBuilder = $params['form_builder'];
		
    $allFields = $formBuilder->all();
    foreach ($allFields as $inputField => $input) {
        $formBuilder->remove($inputField);
    }
		
    foreach ($allFields as $inputField => $input) {
        $formBuilder->add($input);
        if ($inputField == 'email') {
            /** @var TextType::class \Symfony\Component\Form\Extension\Core\Type\TextType */
            $formBuilder->add(
                'agecheck', 
                 TextType::class, 
                 ['label' => 'Age Verified ?']
            );
			$formBuilder->add(
                'agecheckdate', 
                 TextType::class, 
                 ['label' => 'Date of Check']
            );
			
        }
     }
		
		$formBuilder->get('agecheck')->setRequired(false);
		$formBuilder->get('agecheckdate')->setRequired(false);
  }
	
	    
     // Add data to the new field in Customer List
    
    public function hookActionCustomerFormQueryBuilderModifier(array $params)
    {
        $searchQueryBuilder = $params['search_query_builder'];

        $searchCriteria = $params['search_criteria'];
		
		$searchQueryBuilder->addSelect( 'c.`agecheck`' );
		$searchQueryBuilder->addSelect( 'c.`agecheckdate`' );
    } 
	
	
	// ****************************** ORDER LIST ******************************

		// Create hook in Order List
    public function hookActionOrderGridDefinitionModifier(array $params)
    {
        /** @var GridDefinitionInterface $definition */
        $definition = $params['definition'];

        $definition
            ->getColumns()
            ->addAfter(
                'customer',
                (new DataColumn('agecheck'))
                    ->setName($this->l('18?'))
                    ->setOptions([
                        'field' => 'agecheck',
                    ])
		);
    }
	
		    /**
     * Add data to the new field in Order List
     */
    public function hookActionOrderGridQueryBuilderModifier( array $params ) {

		$searchQueryBuilder = $params['search_query_builder'];

        $searchCriteria = $params['search_criteria'];
		
		/** $searchQueryBuilder->addSelect( 'c.`agecheck`' );*/
		$searchQueryBuilder->leftJoin(
            'c',
            '`' . pSQL(_DB_PREFIX_) . 'customer`',
            'pa',
            'pa.`id_customer` = c.`id_customer`
                 = (
                    SELECT
                      pa2.`agecheck`
                    FROM
                      `'. pSQL(_DB_PREFIX_) .'customer` pa2
                    WHERE pa2.`id_customer` = pa.`id_customer`
                    ORDER BY LENGTH(pa2.`agecheck`) DESC
                    LIMIT 1
                )'
        );

    }

	


	
}

I'm not sure what the 'c', 'pa' & 'pa2' refer to, so I'm stuck there at the moment.

I'm also struggling to find the code to make this into a date picker field

$formBuilder->add(
                'agecheckdate', 
                 TextType::class, 
                 ['label' => 'Date of Check']
            );
			
        

 

Edited March 24, 2022 by MerseyRay (see edit history)

[Ray UK]

Actually, I just finally got the field to show as a date field

It's just not getting the data from the db

[Ray UK]

Would anybody know the sql to enter here

$searchQueryBuilder->leftJoin(
            'c',
            '`' . pSQL(_DB_PREFIX_) . 'customer`',
            'pa',
            'pa.`id_customer` = c.`id_customer`
                 = (
                    SELECT
                      pa2.`agecheck`
                    FROM
                      `'. pSQL(_DB_PREFIX_) .'customer` pa2
                    WHERE pa2.`id_customer` = pa.`id_customer`
                    ORDER BY LENGTH(pa2.`agecheck`) DESC
                    LIMIT 1
                )'
        );

    

I need to get the column 'agecheck' from the 'ps_customer' table. The page this will be shown on is the orders list so doing a sql join from orders to customers.

And could somebody explain the format for the sql. ie, what 'c' & 'pa'/'pa2' are for.  Then maybe I can work out the correct query for myself.

Thanks

 

Edited March 28, 2022 by MerseyRay (see edit history)

[knacky]

It's easy.
c = customer table
There is no need to create a JOIN on the same table.
You need to add where.
E.g.

$searchQueryBuilder->addSelect("IF(c.`agecheck` IS NULL, '--', c.`agecheck`) AS `agecheck`");

foreach ($searchCriteria->getFilters() as $filterName => $filterValue) {
	if ($filterName === 'agecheck' && $filterValue) {
		$searchQueryBuilder->andWhere("c.`agecheck` = :agecheck");
		$searchQueryBuilder->setParameter('agecheck', $filterValue);
		if (!$filterValue) {
			searchQueryBuilder->orWhere('c.`agecheck` IS NULL');
		}
	}
}

Of course you need to add your field to formBuilder.

E.g.

$formBuilder = $params['form_builder'];
$formBuilder->add('agecheck', TextType::class, [
	'label' => $this->l('Age ?'),
	'required' => false,
]);

$customerId = $params['id'];
$ageCheck = Db::getInstance()->getValue('SELECT agecheck FROM '._DB_PREFIX_.'customer WHERE id_customer = '.$params['id']);

$params['data']['agecheck'] = $ageCheck;

$formBuilder->setData($params['data']); 

 

[Ray UK]

That's easy for you to say lol...

That part is now working fine. thankyou.

 

I am stuck (been trying most of the day) at getting the agecheckdate to fill in on the same page, and for data to save. agecheckdate is obviously a Datetype::class. The field is showing on the form but won't fill the data. (Jan 1 2017 is showing on all the customers I try)

 

here is my current code for this page

		// ****************************** CUSTOMER PAGE ****************************** 
	
	// Create new column in Customer List
    public function hookActionCustomerFormBuilderModifier(array $params)
    {
    $formBuilder = $params['form_builder'];
		
    $allFields = $formBuilder->all();
    foreach ($allFields as $inputField => $input) {
        $formBuilder->remove($inputField);
    }
		
    foreach ($allFields as $inputField => $input) {
        $formBuilder->add($input);
        if ($inputField == 'email') {
            /** @var TextType::class \Symfony\Component\Form\Extension\Core\Type\TextType */
            $formBuilder->add(
                'agecheck', 
                 TextType::class, 
                 ['label' => 'Age Verified ?']
            );
			$formBuilder->add(
                'agecheckdate', 
                 DateType::class, 
                 ['label' => 'Date of Check']
				
            );
			
        }
     }
		
		// Get data from db and assign it to variables
		$customerId = $params['id'];
		$ageCheck = Db::getInstance()->getValue('SELECT agecheck FROM '._DB_PREFIX_.'customer WHERE id_customer = '.$params['id']);		
		$params['data']['agecheck'] = $ageCheck;
		$formBuilder->setData($params['data']); 


		$ageCheckDate = Db::getInstance()->getValue('SELECT agecheckdate FROM '._DB_PREFIX_.'customer WHERE id_customer = '.$params['id']);
		// $params['data']['agecheckdate'] = $ageCheckDate;   <- IF I ENABLE THIS IT THROWS AN ERROR
		
		$formBuilder->setData($params['data']); 
	}
	
	    
     // Add data to the new field(s) in Customer List
    
    public function hookActionCustomerFormQueryBuilderModifier(array $params)
    {
        $searchQueryBuilder = $params("IF(c.`agecheck` IS NULL, '--', c.`agecheck`) AS `agecheck`");
		
		foreach ($searchCriteria->getFilters() as $filterName => $filterValue) {
			if ($filterName === 'agecheck' && $filterValue) {
				$searchQueryBuilder->andWhere("c.`agecheck` = :agecheck");
				$searchQueryBuilder->setParameter('agecheck', $filterValue);
				if (!$filterValue) {
					$searchQueryBuilder->orWhere('c.`agecheck` IS NULL');
				}
			}
		}
		
		$searchQueryBuilder = $params("IF(c.`agecheckdate` IS NULL, '--', c.`agecheckdate`) AS `agecheckdate`");
		
		foreach ($searchCriteria->getFilters() as $filterName => $filterValue) {
			if ($filterName === 'agecheckdate' && $filterValue) {
				$searchQueryBuilder->andWhere("c.`agecheckdate` = :agecheckdate");
				$searchQueryBuilder->setParameter('agecheckdate', $filterValue);
				if (!$filterValue) {
					$searchQueryBuilder->orWhere('c.`agecheckdate` IS NULL');
				}
			}
		}


    } 

Thanks for your help.. Ill get there one day

[knacky]

https://symfony.com/doc/current/reference/forms/types/date.html#input-format

[Ray UK]

Ok So after hours of studying, I have managed to populate the field 'agecheckdate' with the data from the db

2 things im struggling with now.

1: Saving the data to the database. I am playing with this code but cant get it right.

	// Save the age check data to the db
	public function hookActionAfterUpdateCustomerFormHandler(array $params)
{
    $this->updateCustomerAgeCheck($params);
}
	private function updateCustomerAgeCheck(array $params)
{
    $customerId = $params['id'];
    /** @var array $customerFormData */
    $customerFormData = $params['form_data'];
    $ageCheck = $customerFormData['agecheck'];
    $ageCheckDate = $customerFormData['agecheckdate'];
    
    // implement review status saving here
}

2 Populating the data in the Orders list. Here is the code I'm having trouble getting right.

     // Populate data to the new field in Order List
    
   public function hookActionOrderGridQueryBuilderModifier( array $params ) {

		$searchQueryBuilder = $params['search_query_builder'];

        $searchCriteria = $params['search_criteria'];
		
		//$searchQueryBuilder->addSelect( 'c.`agecheck`' );
		
		$searchQueryBuilder->leftJoin(
            'c',
            '`' . pSQL(_DB_PREFIX_) . 'customer`',
            'o',
            'o.`id_customer` = c.`id_customer`
                 = (
                    SELECT
                      c.`agecheck`
                    FROM
                      `'. pSQL(_DB_PREFIX_) .'customer` 
                    WHERE o.`id_customer` = c.`id_customer`
                )'
        );

    }

any help appreciated

[knacky]

$yearData = $customerFormData['agecheckdate']['year'];

$monthData = $customerFormData['agecheckdate']['month'];

$dayData = $customerFormData['agecheckdate']['day'];

agecheckdate is an array.

 

[Ray UK]

Hi knacky,

I have the data showing in the customer profile page, it just won't save.

I used this to make the field.

			/** @var TextType::class \Symfony\Component\Form\Extension\Core\Type\DateType */
			$formBuilder->add(
                'agecheckdate', 
                 DateType::class, 
				 ['widget' => 'single_text',
				  'input'  => 'string',
				  'label' => 'Date of Check']
				
            );

I get the data from the db and populate it using this

		$ageCheckDate = Db::getInstance()->getValue('SELECT agecheckdate FROM '._DB_PREFIX_.'customer WHERE id_customer = '.$params['id']);
		$params['data']['agecheckdate'] = $ageCheckDate;

     // Populate data to the new field(s) in Customer Page
    
    public function hookActionCustomerFormQueryBuilderModifier(array $params)
    {
		$searchQueryBuilder->addSelect( 'c.`agecheck`' );
        $searchQueryBuilder->addSelect( 'c.`agecheckdate`' );
    } 

but it won't save to the db.  Here is my attempt.

	// Save the age check data to the db
	public function hookActionAfterUpdateCustomerFormHandler(array $params)
{
    $this->updateCustomerAgeCheck($params);
}
	private function updateCustomerAgeCheck(array $params)
{
    $customerId = $params['id'];
    /** @var array $customerFormData */
    $customerFormData = $params['form_data'];
    $ageCheck = $customerFormData['agecheck'];
    $ageCheckDate = $customerFormData['agecheckdate'];
    
}

 

[knacky]

🙃

You make it unnecessarily complicated.

I will upload a functional demonstration of how it is done in administration, I have not tested it in FO.

But it should work.

[Ray UK]

I most probably do lol.

Its a learning curve for me.

The F.O isnt needed anyhow, this is for administration only so that we know the customer is over 18 and has been verified.

Thank you.

 

[knacky]

Only one thing I don't understand is why a new Date Of Check field is created in FO and BO when there is a Birthdate field?

So do you need to save the age when entering / changing the Birthdate in FO and BO?

[Ray UK]

We keep the date of when we carried out the check to confirm they are over 18.

Nothing needs to be showin in the FO

Just the 3 back office pages

Order page: agecheck
Customer list: agecheck
Customer profile in BO: agecheck, agecheckdate

Edited March 30, 2022 by MerseyRay (see edit history)

[knacky]

I understand.
And what to do if the customer changes the date of birth?
It is not a problem to calculate the age and, if it is under the age of 18, to cancel the validation and delete the check date.
Or every time the date of birth changes.

[Ray UK]

The customer create their profile.

We then have to do a check with authorities to confim they are over 18 from the details they provide.

Then we mark it to say "Pass" with the date of check so that we know we can sell to them.

It only for our records

 

[knacky]

Sample module ps_agecheck:

ps_agecheck.zip

[Ray UK]

Thank you so much.

I'll download now and try it.

UPDATE:

Ok so I have it installed but..
all customers have a red cross in the customer list. (the data is still in my db)
on the customer profile, the data does not save.
and lastly, the agecheck column isn't showing on the "Orders List" page

 

I have moved the agecheck to after email where its easier to see and don't need the date on the customer list so commented that out too.
The 18? column on this customer is Pass so should be a tick. Is the data in that column now a 1/0 option instead of Pass/Null

Edited March 31, 2022 by MerseyRay (see edit history)

[knacky]

And you still have your previous module installed?

Values for boolean must be 0/1 !!!

1. Make a backup of the customer table
2. uninstall your previous module
3. uninstall my module
4. install my module
5. check the hooks in position
6. upload a backup of the customer table

[Ray UK]

I have disabled my module whilst trying yours.

Ok so I have made some changes.

Moved both fields to underneath the email on the customer profile.
Updated all Pass values to 1 in the db and that is now showing the Ticks/Crosses correctly in the list, and the switch is also showing correct data in the customer profile.
Changed all occurrences of agedate to agecheckdate as this is the existing column. (Date is now showing correctly in customer profile)

So now everything is back to how I had it earlier, but using your module.

Just agecheck tick/cross is not showing in the Order list

And nothing is saving on customer profile.

NO ERRORS are showing when saving.

I have attached the 1 altered file with the alterations I made

ps_agecheck.zip

Edited March 31, 2022 by MerseyRay (see edit history)

[knacky]

Check if there are hooks !!!
Design> Positions

'actionCustomerGridQueryBuilderModifier',

'actionCustomerGridDefinitionModifier',

'actionAdminCustomersListingFieldsModifier',

 'actionCustomerFormBuilderModifier',

'actionAfterCreateCustomerFormHandler',

'actionAfterUpdateCustomerFormHandler',

[knacky]

I don't know why I load the whole modified module ?

 

[Ray UK]

Yes the hooks seem to be ok

 

Edited March 31, 2022 by MerseyRay (see edit history)

[knacky]

And

./override/classes/Customer.php ?

Edited March 31, 2022 by knacky (see edit history)

[Ray UK]

Ahh, I had changed in the module/override folder, but hadn't in the root/override folder where it's copied to.

Brilliant, so that is now saving. Thank you

Only thing missing is the Column on the Orders List page

[knacky]

Orders List page ?

You didn't mention it when I was writing the demo.

I haven't noticed any of your reactions to posts where the heart is gray and like 😉

You will need to create additional features for the Order List hook.

I've already spent a lot of time here and for free 🤪

 

[Ray UK]

It was on my original post, but its fine you have helped me get this far and its much appreciated.

Hearts have been click for you

Ill attempt the orders list. Its just the sql join I couldnt get right.

Many Thanks

[knacky]

Here's a working solution:

public function hookActionOrderGridQueryBuilderModifier(array $params)
    {

        $searchQueryBuilder = $params['search_query_builder'];

        $searchCriteria = $params['search_criteria'];

        $searchQueryBuilder->addSelect(
            'cu.`agecheck` AS `agecheck`'
        );

        if ('agecheck' === $searchCriteria->getOrderBy()) {
            $searchQueryBuilder->orderBy('cu.`agecheck`', $searchCriteria->getOrderWay());
        }

        foreach ($searchCriteria->getFilters() as $filterName => $filterValue) {
            if ('agecheck' === $searchCriteria->getOrderBy()) {
                $searchQueryBuilder->orderBy('cu.`agecheck`', $searchCriteria->getOrderWay());
            }

            if ($filterName === 'agecheck') {
                $searchQueryBuilder->andWhere("cu.`agecheck` = :agecheck");
                $searchQueryBuilder->setParameter('agecheck', $filterValue);
            }
        }
    }

    public function hookActionOrderGridDefinitionModifier(array $params)
    {
        $definition = $params['definition'];
        
        $definition->getColumns()
            ->addAfter('reference', (new StatusColumn('agecheck'))->setName($this->l('18 ?'))
            ->setOptions(
                ['field' => 'agecheck',]
            )
        );

        $definition->getFilters()->add((new Filter('agecheck', YesAndNoChoiceType::class))
            ->setAssociatedColumn('agecheck')
            ->setTypeOptions([
                'required' => false,
            ])
        );
    }

 

Edited March 31, 2022 by knacky (see edit history)

[Ray UK]

24 minutes ago, knacky said:

bad

was going to say, its showing an error with that code

Price specification not found for currency: ""

[PrestaShop\PrestaShop\Core\Localization\Exception\LocalizationException 0]

[knacky]

Updated script and work !

I haven't tested filtering, but that shouldn't be a problem.

[Ray UK]

Thank you very much its working fantasitc now.

All your help is much appreciated

[Ray UK]

Hi,

module still working fine, thanks again.

Just 1 little annoying problem I have with it.

On customer creation, the "agecheckdate" is always 0000-00-00 by default.
I have tried 2001-01-01 in the Default/As defined: in the sql, but it still puts 0000-00-00 on every new customer.

This brings up an error when trying to edit the customer in B.O because 0000-00-00 isnt a valid date.

How can I either, enter a default date such as 2001-01-01 or not throw an error.

 

(To edit a customer with 0000-00-00 I have to go into the database and enter a valid date so that we can edit the customer in the B.O)

Thank you

Edited June 8, 2022 by MerseyRay (see edit history)

[knacky]

In the database in the ps_customer table, just set the default date for the age_check column.

 

Or SQL query:

ALTER TABLE `ps_customer` ALTER COLUMN `agedate` DATE NOT NULL DEFAULT '2021-01-01';

 

Edited June 11, 2022 by knacky (see edit history)

[Ray UK]

HI Knacky,

I already have the date entered as DEFAULT in the sql table.

But every customer still has 0000-00-00 after account creation.

Could it be that the account creation page set this somewhere?

Thanks

[knacky]

If agecheck has changed to the default date until now, the old records will have 0000-00-00.

[knacky]

UPDATE `ps_customer` SET `agedate` = '2021-01-01' WHERE `agedate` IS NULL OR `agedate` = '0000-00-00';

Back up the ps_customer table before running the command.

[Ray UK]

its not the old records that are causing issues. It's every new customer that registers, their agecheckdate is set to 0000-00-00 even though I have set a DEFAULT in the database

I have the DEFAULT already set in db.

Then I create a NEW customer. In the db they are all set to 0000-00-00

Edited June 13, 2022 by MerseyRay (see edit history)

[knacky]

And where do you see it?
Where is the date 0000-00-00?
In customer editing in BO?
Make a screen.

[Ray UK]

The customer does not see that agecheckdate, that is B.O only.

I have just created a new joe bloggs customer as per the images above.

in the db, 0000-00-00 is the value of agecheckdate, even though I have set it to be 2021-01-01 as the DEFAULT

[knacky]

There is nothing to change the default date to 0000-00-00.
The date change is only in the customer editing in BO !!!

[knacky]

If you have made any adjustments in the module, you have to deal with it yourself. I can't do it like that.

[Ray UK]

I havent changed anything in the module you done for me.

So I have set the default to be 2021-01-01 in the db.

Customer creates a NEW account and the agecheckdate in db is 0000-00-00 like this

I cannot edit that customer record then as I get an error like this

The error is because 0000-00-00 is not a valid date.

 

Maybe the core ps account creation process needs the variable for the agecheckdate otherwise it updates the record with a blank value/0000-00-00

[Ray UK]

If I manually insert a new record directly in the db (a backup one), the date is entered correctly as per the DEFAULT I set

So something else must be setting it

[knacky]

hookActionCustomerFormBuilderModifier

There is an SQL query to the age checkdate table.
Here you can edit the code to return a different date.

$getAgeDate = Db::getInstance()->getValue('SELECT agecheckdate .......
if ($getAgeDate == '0000-00-00' OR $getAgeDate == ''){
	$getAgeDate = '2021-01-01';
}

 

Edited June 13, 2022 by knacky (see edit history)

[Ray UK]

Awesome, that has solved the error issue.

Its still 0000-00-00 to start with in the db, but changes it to 2021-01-01 when I go to edit the customer so no more error.

I had to change slightly  to

if ($getageCheckDate == '0000-00-00' OR $getageCheckDate == ''){
        $getageCheckDate = '2021-01-01';
      }

Thanks so much

[matik4]

@knacky @MerseyRay

Just wanted to say thank you to both of you 🙏  based on your code I was able to make a module that allows editing customer's registered language.