Symfony Profiler

<?php
namespace App\Repository;
use App\Entity\Animal;
use App\Entity\Event;
use App\Entity\EventData;
use App\Entity\Organization;
use App\Entity\Producer;
use App\Entity\Treatment;
use App\Entity\User;
use App\Entity\UserType;
use App\Repository\AnimalRepository;
use App\Repository\Common\FilterBuilder;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityManagerInterface;
use \Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;
use Psr\Container\ContainerInterface;
use Symfony\Component\Security\Core\Authentication\Token\Storage\TokenStorageInterface;
use Symfony\Component\Security\Core\Security;
use Symfony\Contracts\Translation\TranslatorInterface;
/**
* @method Organization|null find($id, $lockMode = null, $lockVersion = null)
* @method Organization|null findOneBy(array $criteria, array $orderBy = null)
* @method Organization[] findAll()
* @method Organization[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class OrganizationRepository extends ServiceEntityRepository
{
use FilterBuilder;
private $security;
private $tokenStorage;
private EventDataRepository|\Doctrine\ORM\EntityRepository $eventDataRepository;
private EntityManagerInterface $em;
private \App\Repository\AnimalRepository|\Doctrine\ORM\EntityRepository $animalRepository;
private UserRepository|\Doctrine\ORM\EntityRepository $userRepository;
private TreatmentRepository|\Doctrine\ORM\EntityRepository $treatmentRepository;
public function __construct(
ManagerRegistry $registry,
EntityManagerInterface $em,
Security $security,
TokenStorageInterface $tokenStorage
) {
parent::__construct($registry, Organization::class);
$this->em = $em;
$this->eventDataRepository = $this->em->getRepository(EventData::class);
$this->animalRepository = $this->em->getRepository(Animal::class);
$this->userRepository = $this->em->getRepository(User::class);
$this->treatmentRepository = $this->em->getRepository(Treatment::class);
$this->security = $security;
$this->tokenStorage = $tokenStorage;
}
public function addAnimal( $qb, $aId = null )
{/*{{{*/
$qb->leftJoin('App\Entity\Animal', 'a', "with", "o.id = a.organization");
$qb->andWhere('(a.deleted = 0) OR a.id IS NULL');
$aId and $qb->andWhere("a.id = :aId")->setParameter( 'aId', $aId );
return $qb;
}/*}}}*/
public function addOrganization( $qb, int $org = null )
{/*{{{*/
$org and $qb->andWhere('o.id = :org')->setParameter('org', $org);
return $qb;
}/*}}}*/
public function countBlackList( $qb, $to_date = null )
{/*{{{*/
$to_date = $to_date ?? new \DateTime();
return $qb->addSelect('SUM( if ( a.lackPeriod > :date, 1, 0) ) AS blackList')->setParameter( ':date', $to_date );
}/*}}}*/
public function addActiveAnimals( $qb )
{/*{{{*/
return $qb->andWhere('(a.status = :statusActive) OR a.id is NULL ')->setParameter('statusActive', Animal::STATUS_ACTIVE);
}/*}}}*/
public function addCurrentLot( $qb, Lot $lot = null )
{/*{{{*/
$qb->leftJoin('a.currentLot', 'l', "with", "a.currentLot = l.id")
->andWhere('(l.deleted = 0 and l.status = 1) or l.id is null');
$lot and $qb->andWhere("l.id = :lot")->setParameter( 'lot', $lot );
return $qb;
}/*}}}*/
public function countAnimal( $qb )
{/*{{{*/
return $qb->addSelect('(
SELECT COUNT(DISTINCT aC.id)
FROM App\Entity\Animal aC
-- ahora queda animal -> lot -> farm -> organization
-- que pertenezca al lote
INNER JOIN App\Entity\Lot lC WITH aC.currentLot = lC AND (aC.currentLot = lC.id)
-- que pertenezca al establecimiento
INNER JOIN App\Entity\Farm fC WITH lC.farm = fC.id
-- que pertenezca a la organizacion
INNER JOIN App\Entity\Organization oC WITH fC.organization = oC.id
WHERE aC.deleted = 0
AND aC.status = :statusActive
AND (a.createdAt >= a.deletedAt OR a.updatedAt >= a.deletedAt OR a.deletedAt is NULL)
AND oC.id = o.id
AND fC.deleted = false
AND lC.deleted = false
AND lC.status = true
) as countAnimal')
->setParameter('statusActive', Animal::STATUS_ACTIVE);
}/*}}}*/
public function countSubSelect( $qb, $organizationId = null )
{/*{{{*/
$this->countAnimal( $qb );
$qb->addSelect('(
SELECT COUNT(t.id)
FROM App\Entity\Treatment t
JOIN App\Entity\Farm ft WITH ( t.farm = ft.id AND ft.deleted = 0 )
WHERE t.deleted = 0 AND ( ft.organization = o.id or o.id is null)
) as countTreatment'
);
$qb->addSelect('(
SELECT COUNT(distinct f.id)
FROM App\Entity\Farm f
INNER JOIN App\Entity\Lot lf WITH ( lf.farm = f.id AND lf.deleted = 0 )
WHERE f.organization = o.id and f.deleted = 0 AND lf.status = 1
and f.createdAt is not NULL
) as countFarm'
);
$qb->addSelect('(
SELECT COUNT(ed.id)
FROM App\Entity\EventData ed
JOIN App\Entity\Lot led WITH ( ed.lot = led.id AND led.deleted = 0 )
JOIN App\Entity\Farm fed WITH ( led.farm = fed.id AND fed.deleted = 0 )
WHERE fed.organization = o.id and ed.deleted = 0
AND fed.deleted = 0
AND led.deleted = 0
AND led.status = 1
) as countEventData'
);
$qb->addSelect('(
SELECT COUNT(cl.id)
FROM App\Entity\Lot cl
JOIN App\Entity\Farm fc WITH ( cl.farm = fc.id AND fc.deleted = 0 )
WHERE fc.organization = o.id and cl.deleted = 0
AND fc.deleted = 0
AND cl.deleted = 0
AND cl.status = 1
) as countLot'
);
$qb->addSelect('(
SELECT COUNT(us.id)
FROM App\Entity\User us
WHERE us.organization = o.id and us.enabled = 1
) as countUser'
);
return $qb;
}/*}}}*/
public function groupByOrganization( $qb )
{/*{{{*/
return $qb->addGroupBy('o.id');
}/*}}}*/
/* main Querys */
public function getMainSelects( $qb, $organizationId ): QueryBuilder
{/*{{{*/
/* selects */
$qb->select("o.id as id");
$qb->addSelect("o.name as name");
$qb->addSelect("o.type as type");
$qb->addSelect("o.description as description");
$qb->addSelect("o.logo as logo");
//$qb->addSelect("o.users as users");
$qb->addSelect("o.created as created");
$qb->addSelect("o.cuit as cuit");
$qb->addSelect("o.email as email");
$qb->addSelect("o.phone as phone");
$qb->addSelect("o.businessName as businessName");
//$qb->addSelect("o.address as address");
$qb->addSelect("o.listType as listType");
$qb = $this->countBlackList( $qb );
$qb = $this->countSubSelect( $qb, $organizationId );
/* CUD timestamps */
$qb->addSelect('o.createdAt as createdAt');
$qb->addSelect('o.updatedAt as updatedAt');
$qb->addSelect('o.deletedAt as deletedAt');
return $qb;
}/*}}}*/
public function getMainJoins( $qb, $organizationId ): QueryBuilder
{/*{{{*/
$animalRepository = $this->em->getRepository(Animal::class);
// dd( $animalRepository->getSubJoins( $animalRepository->createQueryBuilder('a'), $organizationId )->select('a.id')->getQuery()->getSQL() );
/* joins */
$qb = $this->addOrganization( $qb, $organizationId );
$qb = $this->addAnimal( $qb );
if ( $organizationId )
$qb = $this->groupByOrganization( $qb );
/*
$qb->leftJoin(
sprintf('(%s)', $animalRepository->getSubJoins(
$animalRepository->createQueryBuilder('a'), $organizationId )
->select('a.id')->getQuery()->getSQL()),
'aCount',
'o.id = aCount.oId' );
*/
return $qb;
}/*}}}*/
public function getMainQuery( $qb, $organizationId ): QueryBuilder
{/*{{{*/
$qb = $this->getMainSelects( $qb, $organizationId );
$qb = $this->getMainJoins( $qb, $organizationId );
return $qb;
}/*}}}*/
public function getListQuery( $organizationId ): QueryBuilder
{/*{{{*/
$qb = $this->createQueryBuilder('o');
$qb = $this->getMainQuery( $qb, $organizationId );
$qb = $this->groupByOrganization( $qb );
return $qb;
}/*}}}*/
public function getStatsQuery( $organizationId ): QueryBuilder
{/*{{{*/
$qb = $this->createQueryBuilder('o');
$qb = $this->getMainQuery( $qb, $organizationId );
$qb->addSelect( 'count(o.id) as countOrganization');
/* es una vista con funciones sobre consultas JSON */
/* DEBUG: falta completar */
return $qb;
}/*}}}*/
public function entryHelp( $organizationId = null )
{/*{{{*/
$qb = $this->createQueryBuilder('o');
$qb = $this->addOrganization( $qb, $organizationId );
if ( $organizationId )
$qb = $this->groupByOrganization( $qb );
return $qb;
}/*}}}*/
/* de Logica Ligera Inc */
public function findOrganizationsByUser(User $user)
{/*{{{*/
$qb = $this->createQueryBuilder('o');
if ($this->security->isGranted("ROLE_MANAGER") && !$this->security->isGranted("ROLE_ADMIN")) {
$qb->join('App\\Entity\\User', 'u')
->where('u.organization = o.id')
->andWhere('u.organization = :organization')
->setParameter(':organization', $user->getOrganization());
} else if ($this->security->isGranted("ROLE_USER") && !$this->security->isGranted("ROLE_ADMIN")) {
$qb->join('App\\Entity\\User', 'u')
->where('u.organization = o.id')
->andWhere('u.id = :user')
->setParameter('user', $user);
}
$qb->orderBy('o.name', 'ASC');
return $qb->getQuery()->getResult();
}/*}}}*/
public function findActiveOrganizations($limit, $offset): array
{/*{{{*/
return $this->createQueryBuilder('o')
->join('App\Entity\EventData', 'ed', "with", "ed.organization = o.id")
->groupBy('o.id')
->setMaxResults($limit)
->setFirstResult($offset)
->getQuery()
->getResult();
}/*}}}*/
public function getTotalActiveOrganizations() : int
{/*{{{*/
return $this->createQueryBuilder('o')
->select('COUNT(DISTINCT o.id)')
->join('App\Entity\EventData', 'ed', "with", "ed.organization = o.id")
->getQuery()
->getSingleScalarResult();
}/*}}}*/
/* Migrado de OrganizationService */
public function exist(Organization $organization)
{/*{{{*/
$name = $organization->getName();
$orgName = $this->findOneBy(array('name' => $name, 'description' => $organization->getDescription()));
if ($orgName != null) {
return $orgName;
}
return null;
}/*}}}*/
public function checkUserType(UserType $userType, $user)
{/*{{{*/
if ($userType->getType() === 'PRODUCTOR') {
$producer = new Producer();
$producer->setUser($user);
$producer->setName($user->getUserName());
$producer->setBusinessName($user->getUserName());
$producer->setToUpdate(true);
$this->em->persist($producer);
$this->em->flush();
}
}/*}}}*/
public function create(Organization $organization)
{/*{{{*/
if (!$this->exist($organization)) {
$this->em->persist($organization);
$this->em->flush();
} else {
$organization = $this->exist($organization);
}
return $organization;
}/*}}}*/
public function new ($username)
{/*{{{*/
$organization = new Organization();
$organization->setName($username);
$organization->setDescription($username);
$organization->setType(Organization::TYPE_COMPANY);
return $organization;
}/*}}}*/
public function update(Organization $organization)
{/*{{{*/
$this->em->flush();
return $organization;
}/*}}}*/
}

src/Repository/OrganizationRepository.php line 43