ap2i/old/Entity/Core/Repository/ContactPointRepository.php

<?php

namespace AppBundle\Entity\Core\Repository;

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

/**
 * Description of BankAccountRepository
 *
 */
class ContactPointRepository extends EntityRepository
{
    public function findInvalidContactPointByOrganization(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.emailInvalid'), $queryBuilder->expr()->neq('o.emailInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.faxNumberInvalid'), $queryBuilder->expr()->neq('o.faxNumberInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.telphoneInvalid'), $queryBuilder->expr()->neq('o.telphoneInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.mobilPhoneInvalid'), $queryBuilder->expr()->neq('o.mobilPhoneInvalid', ':empty'))
                ))
            ->setParameter('organization', $organization->getId())
            ->setParameter('empty', '');

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

    public function findInvalidContactPointByAccess(Organization $organization)
    {
        $queryBuilder = $this->_em->createQueryBuilder();
        $queryBuilder
            ->select('access')
            ->from('AppBundle:AccessAndFunction\Access', 'access')
            ->andWhere('access.organization = :organization')
            ->setParameter('organization', $organization->getId());

        $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.emailInvalid'), $queryBuilder->expr()->neq('o.emailInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.faxNumberInvalid'), $queryBuilder->expr()->neq('o.faxNumberInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.telphoneInvalid'), $queryBuilder->expr()->neq('o.telphoneInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.mobilPhoneInvalid'), $queryBuilder->expr()->neq('o.mobilPhoneInvalid', ':empty'))
                ))
            ->setParameter('ids', $ids)
            ->setParameter('empty', '');

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

    public function findInvalidContactPointByPlace(Organization $organization)
    {
        $queryBuilder = $this->_em->createQueryBuilder();
        $queryBuilder
            ->select('place')
            ->from('AppBundle:Place\Place', 'place')
            ->andWhere('place.organization = :organization')
            ->setParameter('organization', $organization->getId());

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

        $queryBuilder = $this->createQueryBuilder('o');
        $queryBuilder
            ->innerJoin('o.place', 'place')
            ->andWhere(
                $queryBuilder->expr()->in('place', ':ids'),
                $queryBuilder->expr()->orX(
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.emailInvalid'), $queryBuilder->expr()->neq('o.emailInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.faxNumberInvalid'), $queryBuilder->expr()->neq('o.faxNumberInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.telphoneInvalid'), $queryBuilder->expr()->neq('o.telphoneInvalid', ':empty')),
                    $queryBuilder->expr()->andX($queryBuilder->expr()->isNotNull('o.mobilPhoneInvalid'), $queryBuilder->expr()->neq('o.mobilPhoneInvalid', ':empty'))
                ))
            ->setParameter('ids', $ids)
            ->setParameter('empty', '');

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