Symfony Profiler

<?php
namespace App\Repository\__Contact\contact;
use App\Entity\__Contact\contact\Contact;
use App\Entity\Booking\AdvBookingChange;
use App\Entity\Email\Email;
use App\Helper\ParticipantHelper;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\Driver\Exception;
use Doctrine\Persistence\ManagerRegistry;
/**
* @method Contact|null find($id, $lockMode = null, $lockVersion = null)
* @method Contact|null findOneBy(array $criteria, array $orderBy = null)
* @method Contact[] findAll()
* @method Contact[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class ContactRepository extends ServiceEntityRepository
{
public const CONTACT_GENDER = [
1 => "H",
2 => "F",
];
public function __construct(ManagerRegistry $registry)
{
parent::__construct($registry, Contact::class);
}
public function findByEmail(string $email): array
{
return $this->createQueryBuilder('c')
->where('c.professionalEmail = :email OR c.personalEmail = :email')
->setParameter('email', $email)
->getQuery()
->getResult();
}
public function findOneByEmail(string $email): ?Contact
{
$qb = $this->createQueryBuilder('c');
return $qb
->where($qb->expr()->orX(
$qb->expr()->eq('c.professionalEmail', ':email'),
$qb->expr()->eq('c.personalEmail', ':email')
))
->setParameter('email', $email)
->getQuery()
->setMaxResults(1)
->getOneOrNullResult();
}
/**
* TODO : Retourne un user donc devrait plutôt se trouver dans le UserRepository + Régler les dépréciations.
*
* Retourne le user du dernier devis.
*
* @throws DBALException|Exception
*/
public function getUserOfLastQuote(Contact $contact): array
{
$conn = $this->getEntityManager()->getConnection();
/* @lang text */
$sql = '
SELECT u.login
FROM contact c
JOIN booking_participant pp ON pp.contact_id = c.id
JOIN booking p ON p.id = pp.booking_id
JOIN quote q ON q.booking_id = p.id
JOIN security_users u ON u.id = q.seller_id
WHERE c.id = :contactId
ORDER BY q.created_at DESC
LIMIT 1';
$stmt = $conn->prepare($sql);
$stmt->execute(['contactId' => $contact->getId()]);
return $stmt->fetchAll();
}
/**
* TODO : Régler les dépréciations.
*
* Retourne les lignes de factures et règlements pour un contact.
*
* @throws Exception
* @throws \Doctrine\DBAL\Exception
*/
public function getAllLinesforContact(Contact $contact): array
{
$conn = $this->getEntityManager()->getConnection();
/* @lang text */
$sql = "
SELECT
i.id,
i.number AS number,
bp.contact_id,
i.updated_at AS status_line,
sum(il.amount*il.quantity) as amount,
il.description as name,
'invoice' as origin
FROM invoice_line il
JOIN invoice_invoice_line iil on iil.invoice_line_id = il.id
JOIN invoice i on i.id = iil.invoice_id
JOIN booking_participant bp on bp.id = i.payer_id
WHERE bp.contact_id = :contactId
AND i.is_current = 1
GROUP BY i.id
UNION
SELECT
p.id,
p.status AS number,
p.contact_id,
p.status_at AS status_line,
p.amount AS amount,
p.mode as name,
'payment' as origin
FROM payment p
where p.contact_id = :contactId
ORDER BY status_line DESC
LIMIT 30
";
$stmt = $conn->prepare($sql);
$stmt->execute(['contactId' => $contact->getId()]);
return $stmt->fetchAll();
}
/**
* Retourne le prénom et le nom de chaque filleul du contact, ainsi que la date du parrainage.
*/
public function getSponsored(Contact $contact): array
{
return $this->createQueryBuilder('c')
->select('f.firstName, f.lastName, s.sponsoredAt')
->join('c.sponsors', 's')
->join('s.sponsoredParty', 'f')
->andWhere('c.id = :contact')
->setParameter('contact', $contact)
->getQuery()
->getResult();
}
/**
* Retourne le prénom et le nom de chaque parrain du contact, ainsi que la date du parrainage.
*/
public function getSponsors(Contact $contact): array
{
return $this->createQueryBuilder('c')
->select('p.firstName, p.lastName, s.sponsoredAt')
->join('c.sponsoredParties', 's')
->join('s.sponsor', 'p')
->andWhere('c.id = :contact')
->setParameter('contact', $contact)
->getQuery()
->getResult();
}
/**
* @return Contact[]
*/
public function findByAdvBookingChange(AdvBookingChange $advBookingChange): array
{
return $this->createQueryBuilder('c')
->join('c.participants', 'bp')
->join('bp.booking', 'b')
->join('b.advBookingChangeAssociations', 'abca')
->join('abca.bookingChange', 'abc')
->where('abc.id = :advBookingChangeId')
->andWhere('bp.roles LIKE :ownerRole')
->setParameter('advBookingChangeId', $advBookingChange->getId())
->setParameter('ownerRole', '%'.ParticipantHelper::OWNER_ROLE.'%')
->getQuery()
->getResult();
}
/**
* @return Contact[]
*/
public function findByEmailObject(Email $email): array
{
return $this->createQueryBuilder('c')
->join('c.attributedSentEmails', 'ase')
->join('ase.sentEmail', 'se')
->join('se.email', 'e')
->where('e.id = :emailId')
->setParameter('emailId', $email->getId())
->getQuery()
->getResult();
}
/**
* Update les catogry pour les customer
* @throws \Doctrine\DBAL\Exception|Exception
*/
public function updateCategoryCustomer()
{
$conn = $this->getEntityManager()
->getConnection();
$sql = "
UPDATE contact c
LEFT JOIN booking_participant bp on bp.contact_id = c.id
RIGHT JOIN booking b on b.first_contact_id = bp.id
SET c.category='customer'
WHERE bp.contact_id is not null;
";
$stmt = $conn->prepare($sql);
return $stmt->executeStatement();
}
/**
* Update les
* @throws \Doctrine\DBAL\Exception|Exception
*/
public function updateCategoryPassenger():int
{
$conn = $this->getEntityManager()
->getConnection();
$sql = "
UPDATE contact c SET c.category='passenger' WHERE c.id IN
(
SELECT c.id FROM contact c
LEFT JOIN booking_participant bp on bp.contact_id = c.id
WHERE c.category= 'prospect'
AND bp.contact_id is not null
group BY c.id
);
";
$stmt = $conn->prepare($sql);
return $stmt->executeStatement();
}
public function tryToFind(string $email, string $firstName, string $lastName): array
{
$qb = $this->createQueryBuilder('c');
return $qb
->where($qb->expr()->orX(
$qb->expr()->like('c.professionalEmail', ':email'),
$qb->expr()->like('c.personalEmail', ':email')
))
->andWhere($qb->expr()->like('c.firstName', ':firstName'))
->andWhere($qb->expr()->like('c.lastName', ':lastName'))
->andWhere($qb->expr()->eq('c.active', true))
->setParameter('email', $email.'%')
->setParameter('firstName', $firstName.'%')
->setParameter('lastName', $lastName.'%')
->getQuery()
->getResult();
}
}

src/Repository/__Contact/contact/ContactRepository.php line 29