LoginCidadao\CoreBundle\Entity\PersonRepository

Complexity

Total Complexity

21

Size/Duplication

Total Lines	230
Duplicated Lines	0 %

Test Coverage

Coverage

0%

Importance

Changes

0

16 Methods

Rating	Name	Duplication	Size	Complexity
A	getUserSearchQuery()	0	8	1
A	countByPhone()	0	14	3
A	getNameSearchQuery()	0	9	1
A	getFindAuthorizedByClientIdQuery()	0	17	1
A	getSmartSearchQuery()	0	24	4
A	getPhoneSearchQuery()	0	5	1
A	findUnconfirmedEmailUntilDate()	0	8	1
A	getFindByIdIn()	0	6	1
A	getCpfSearchQuery()	0	5	1
A	getBaseSearchQuery()	0	4	1
A	getEmailSearchQuery()	0	5	1
A	findAllPendingCPF()	0	5	1
A	getCountByState()	0	23	1
A	getCountByCity()	0	25	1
A	getCountAll()	0	9	1
A	getCountByCountry()	0	17	1

<?php
/**
 * This file is part of the login-cidadao project or it's bundles.
 *
 * (c) Guilherme Donato <guilhermednt on github>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace LoginCidadao\CoreBundle\Entity;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\ORM\NoResultException;
use Egulias\EmailValidator\EmailValidator;
use Egulias\EmailValidator\Validation\RFCValidation;
use libphonenumber\PhoneNumber;
use libphonenumber\PhoneNumberFormat;
use libphonenumber\PhoneNumberUtil;
use Misd\PhoneNumberBundle\Doctrine\DBAL\Types\PhoneNumberType;

class PersonRepository extends EntityRepository
{

    public function findAllPendingCPF()
    {
        return $this->getEntityManager()
            ->createQuery('SELECT p FROM LoginCidadaoCoreBundle:Person p WHERE p.cpf IS NULL')
            ->getResult();
    }

    public function findUnconfirmedEmailUntilDate(\DateTime $dateLimit)
    {
        return $this->getEntityManager()
            ->createQuery(
                'SELECT p FROM LoginCidadaoCoreBundle:Person p WHERE p.emailConfirmedAt IS NULL AND p.emailExpiration <= :date'
            )
            ->setParameter('date', $dateLimit)
            ->getResult();
    }

    public function getFindAuthorizedByClientIdQuery($clientId)
    {
        return $this->createQueryBuilder('p')
            ->innerJoin(
                'LoginCidadaoCoreBundle:Authorization',
                'a',
                'WITH',
                'a.person = p'
            )
            ->innerJoin(
                'LoginCidadaoOAuthBundle:Client',
                'c',
                'WITH',
                'a.client = c'
            )
            ->andWhere('c.id = :clientId')
            ->setParameter('clientId', $clientId);
    }

    public function getCountByCountry()
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        return $qb
            ->select('count(p.country) AS qty, c.name')
            ->from('LoginCidadaoCoreBundle:Person', 'p')
            ->innerJoin(
                'LoginCidadaoCoreBundle:Country',
                'c',
                'WITH',
                'p.country = c'
            )
            ->where('p.country IS NOT NULL')
            ->groupBy('p.country, c.name')
            ->orderBy('qty', 'DESC')
            ->getQuery()->getResult();
    }

    public function getCountByState()
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        return $qb
            ->select('count(p.state) AS qty, s.id, s.name, c.name AS country')
            ->from('LoginCidadaoCoreBundle:Person', 'p')
            ->innerJoin(
                'LoginCidadaoCoreBundle:State',
                's',
                'WITH',
                'p.state = s'
            )
            ->innerJoin(
                'LoginCidadaoCoreBundle:Country',
                'c',
                'WITH',
                's.country = c'
            )
            ->where('p.state IS NOT NULL')
            ->groupBy('p.state, s.name, country, s.id')
            ->orderBy('qty', 'DESC')
            ->getQuery()->getResult();
    }

    public function getCountByCity($stateId)
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        return $qb
            ->select('count(p.city) AS qty, c.name')
            ->from('LoginCidadaoCoreBundle:Person', 'p')
            ->innerJoin(
                'LoginCidadaoCoreBundle:City',
                'c',
                'WITH',
                'p.city = c'
            )
            ->innerJoin(
                'LoginCidadaoCoreBundle:State',
                's',
                'WITH',
                'c.state = s'
            )
            ->where('p.city IS NOT NULL')
            ->andWhere('s.id = :stateId')
            ->groupBy('p.city, c.name')
            ->orderBy('qty', 'DESC')
            ->setParameter('stateId', $stateId)
            ->getQuery()->getResult();
    }

    public function getCountAll()
    {
        $qb = $this->getEntityManager()->createQueryBuilder();

        return $qb
            ->select('count(p.id) AS qty')
            ->from('LoginCidadaoCoreBundle:Person', 'p')
            ->orderBy('qty', 'DESC')
            ->getQuery()->getSingleResult();
    }

    public function getUserSearchQuery($query)
    {
        return $this->createQueryBuilder('p')
            ->where(
                'p.cpf LIKE :query OR p.username LIKE :query OR p.email LIKE :query OR p.firstName LIKE :query OR p.surname LIKE :query'
            )
            ->setParameter('query', '%'.addcslashes($query, '\\%_').'%')
            ->addOrderBy('p.id', 'DESC');
    }

    public function getFindByIdIn($ids)
    {
        return $this->createQueryBuilder('p')
            ->where('p.id in(:ids)')
            ->setParameters(compact('ids'))
            ->addOrderBy('p.id', 'desc');
    }

    /**
     * @param PhoneNumber $phone
     * @return int
     */
    public function countByPhone(PhoneNumber $phone)
    {
        try {
            $phoneUtil = PhoneNumberUtil::getInstance();

            return $this->createQueryBuilder('p')
                ->select('COUNT(p)')
                ->where('p.mobile = :mobile')
                ->setParameter('mobile', $phoneUtil->format($phone, PhoneNumberFormat::E164))
                ->getQuery()->getSingleScalarResult();
        } catch (NoResultException $e) {
            return 0;
        } catch (NonUniqueResultException $e) {
            return 0;
        }
    }

    /**
     * @return \Doctrine\ORM\QueryBuilder
     */
    private function getBaseSearchQuery()
    {
        return $this->createQueryBuilder('p')
            ->addOrderBy('p.id', 'DESC');
    }

    public function getCpfSearchQuery($cpf)
    {
        return $this->getBaseSearchQuery()
            ->where('p.cpf = :cpf')
            ->setParameter('cpf', $cpf);
    }

    public function getEmailSearchQuery($email)
    {
        return $this->getBaseSearchQuery()
            ->where('p.emailCanonical = LOWER(:email)')
            ->setParameter('email', $email);
    }

    public function getNameSearchQuery($name)
    {
        $sanitized = addcslashes($name, "%_");

        return $this->getBaseSearchQuery()
            ->where('LowerUnaccent(p.username) LIKE LowerUnaccent(:name)')
            ->orWhere('LowerUnaccent(p.firstName) LIKE LowerUnaccent(:name)')
            ->orWhere('LowerUnaccent(p.surname) LIKE LowerUnaccent(:name)')
            ->setParameter('name', "%{$sanitized}%");
    }

    public function getPhoneSearchQuery(PhoneNumber $phoneNumber)
    {
        return $this->getBaseSearchQuery()
            ->where('p.mobile = :mobile')
            ->setParameter('mobile', $phoneNumber, PhoneNumberType::NAME);
    }

    /**
     * This will return the appropriate query for the input given.
     * @param $query
     * @return \Doctrine\ORM\QueryBuilder
     */
    public function getSmartSearchQuery($query)
    {
        $query = trim($query);

        if (strlen($query) == 0) {
            return $this->getBaseSearchQuery();
        }

        $cpfRegex = '/^(?:\d{3}\.?){2}\d{3}-?\d{2}$/';
        // Check CPF
        if (preg_match($cpfRegex, $query) === 1) {
            $cpf = str_replace(['.', '-'], '', $query);

            return $this->getCpfSearchQuery($cpf);
        }

        // Check email
        $emailValidator = new EmailValidator();
        if ($emailValidator->isValid($query, new RFCValidation())) {
            return $this->getEmailSearchQuery($query);
        }

        // Defaults to name search
        return $this->getNameSearchQuery($query);
    }
}