ap2i/old/Entity/Bank/Repository/BankAccountRepository.php

<?php

namespace AppBundle\Entity\Bank\Repository;

use AppBundle\Entity\Organization\Organization;
use Doctrine\ORM\EntityRepository;

/**
 * Description of BankAccountRepository
 *
 */
class BankAccountRepository extends EntityRepository
{
    public function findInvalidBankAccountByOrganization(Organization $organization)
    {
        $queryBuilder = $this->createQueryBuilder('o');
        $queryBuilder
            ->innerJoin('o.organization', 'organization')
            ->andWhere('organization.id = :organization')
            ->andWhere(
                $queryBuilder->expr()->orX(
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.bicInvalid'), $queryBuilder->expr()->neq('o.bicInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.ibanInvalid'), $queryBuilder->expr()->neq('o.ibanInvalid', ':empty'))
                ))
            ->setParameter('organization', $organization->getId())
            ->setParameter('empty', '');

        return $queryBuilder->getQuery()->getResult();
    }

    public function findInvalidBankAccountByAccess(Organization $organization)
    {
        $queryBuilder = $this->_em->createQueryBuilder();
        $queryBuilder
            ->select('access')
            ->from('AppBundle:AccessAndFunction\Access', 'access')
            ->innerJoin('access.organizationFunction', 'function')
            ->andWhere('access.organization = :organization')
            ->andWhere('function.startDate < :dateEnd')
            ->andWhere(
                $queryBuilder->expr()->orX(
                    $queryBuilder->expr()->gt('function.endDate', ':dateStart'),
                    $queryBuilder->expr()->isNull('function.endDate')
                ))
            ->setParameter('organization', $organization->getId())
            ->setParameter('dateStart', date('Y-m-d'))
            ->setParameter('dateEnd', date('Y-m-d'));

        $ids = array_map(function($o) {return $o->getPerson()->getId();}, $queryBuilder->getQuery()->getResult());

        $queryBuilder = $this->createQueryBuilder('o');
        $queryBuilder
            ->innerJoin('o.person', 'person')
            ->andWhere(
                $queryBuilder->expr()->in('person', ':ids'),
                $queryBuilder->expr()->orX(
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.bicInvalid'), $queryBuilder->expr()->neq('o.bicInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.ibanInvalid'), $queryBuilder->expr()->neq('o.ibanInvalid', ':empty'))
                ))
            ->setParameter('ids', $ids)
            ->setParameter('empty', '');

        return $queryBuilder->getQuery()->getResult();
    }
}