Symfony Profiler

<?php
namespace App\Repository;
use App\Controller\Common\MetadataBuilder;
use App\Entity\Animal;
use App\Entity\Farm;
use App\Entity\Lot;
use App\Entity\Organization;
use App\Entity\User;
use App\Repository\Common\FilterBuilder;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\ORM\EntityManagerInterface;
/**
* @method Farm|null find($id, $lockMode = null, $lockVersion = null)
* @method Farm|null findOneBy(array $criteria, array $orderBy = null)
* @method Farm[] findAll()
* @method Farm[] findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
*/
class FarmRepository extends ServiceEntityRepository
{
use FilterBuilder;
const PREGNANTS = [1,2,3];
private EntityManagerInterface $em;
public function __construct(ManagerRegistry $registry, EntityManagerInterface $em)
{/*{{{*/
parent::__construct($registry, Farm::class);
$this->em = $em;
}/*}}}*/
public function addFarm( $qb, $fId = null )
{/*{{{*/
$fId and $qb->andWhere('f.id = :fId')->setParameter('fId', $fId);
return $qb->andWhere('f.deleted = 0');
}/*}}}*/
public function addOrganization( $qb, int $org = null )
{/*{{{*/
$qb->join('App\Entity\Organization', 'o', "with", "f.organization = o.id");
$qb->addSelect('o.id as organizationId' );
$org and $qb->andWhere('o.id = :org')->setParameter('org', $org);
return $qb;
}/*}}}*/
public function addProducer( $qb, $producer = null )
{/*{{{*/
$qb->leftJoin('f.producer', 'p', "with", "f.producer = p.id");
$producer and $qb->andWhere('p.id = :producer')->setParameter( 'producer', $producer );
return $qb;
}/*}}}*/
public function countLot( $qb )
{/*{{{*/
return $qb->addSelect( '(
SELECT COUNT(cl.id)
FROM App\Entity\Lot cl
INNER JOIN App\Entity\Farm fc WITH ( cl.farm = fc.id )
WHERE fc.organization = o.id
AND fc.id = f.id
and cl.deleted = 0
AND fc.deleted = 0
AND cl.deleted = 0
AND cl.status = 1
) as countLot' );
}/*}}}*/
public function countAnimal( $qb, $organizationId = null, $farmId = null )
{/*{{{*/
$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 lCA WITH aC.currentLot = lCA AND (aC.currentLot = lCA.id)
-- que pertenezca al establecimiento
INNER JOIN App\Entity\Farm fCA WITH lCA.farm = fCA.id
-- que pertenezca a la organizacion
INNER JOIN App\Entity\Organization oC WITH fCA.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 fCA.deleted = false
AND lCA.deleted = false
AND lCA.status = true
AND fCA.id = f.id
) as countAnimal")
->setParameter('statusActive', Animal::STATUS_ACTIVE);
return $qb;
}/*}}}*/
public function addLot( $qb, $lot = NULL)
{/*{{{*/
$qb->leftJoin('App\Entity\Lot', 'l', "with", "f.id = l.farm");
$qb->addSelect('l.name as lotName');
$lot and $qb->andWhere("l.id = :lot")->setParameter( 'lot', $lot );
return $qb;
}/*}}}*/
public function addAddress( $qb, $addrId = null )
{/*{{{*/
$qb->leftJoin('App\Entity\Address', 'addr', "with", "f.address = addr.id");
$addrId and $qb->andWhere("addr.id = :addrId")->setParameter( 'addrId', $addrId );
return $qb;
}/*}}}*/
public function addActiveAnimals( $qb )
{/*{{{*/
return $qb->andWhere('(a.deleted = 0 AND a.status = :statusActive) OR a.id is NULL ')->setParameter('statusActive', Animal::STATUS_ACTIVE);
}/*}}}*/
public function addAnimal( $qb, $aId = null )
{/*{{{*/
$qb->leftJoin('App\Entity\Animal', 'a', "with", "l.id = a.currentLot");
$aId and $qb->andWhere("a.id = :aId")->setParameter( 'aId', $aId );
return $qb;
}/*}}}*/
public function addBlackList( $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 addCurrentPregnancyStatus( $qb, $cps = null )
{/*{{{*/
$qb->addSelect('SUM( if ( a.currentPregnancyStatus in (:cps), 1, 0) ) AS currentPregnants')->setParameter( ':cps', $cps );
$qb->addSelect('SUM( if ( a.currentPregnancyStatus not in (:cps), 1, 0) ) AS currentEmpty')->setParameter( ':cps', $cps );
return $qb;
}/*}}}*/
public function groupByFarm( $qb )
{/*{{{*/
return $qb->addGroupBy('f.id');
}/*}}}*/
public function groupByLot( $qb )
{/*{{{*/
return $qb->addGroupBy('l.id');
}/*}}}*/
/* getListQuery */
public function getListQuery( $organizationId, $farm = null ): \Doctrine\ORM\QueryBuilder
{/*{{{*/
$qb = $this->createQueryBuilder('f');
$qb->select('f.id as id');
$qb->addSelect('f.uuid as uuid');
$qb->addSelect('f.name as name');
$qb->addSelect('f.picture as picture');
$qb->addSelect('f.hectares as hectares');
$qb->addSelect('f.renspa as renspa');
$qb->addSelect('f.cuig as cuig');
$qb->addSelect('f.cuartel as cuartel');
$qb->addSelect('f.tenure as tenure');
$qb->addSelect('f.cottage as cottage');
$qb->addSelect('f.tambo as tambo');
$qb->addSelect('f.raise as raise');
$qb->addSelect('f.feedlot as feedlot');
$qb->addSelect('f.wintering as wintering');
$qb->addSelect('f.mix as mix');
$qb->addSelect('f.other as other');
$qb->addSelect('f.timeStamp as timeStamp');
$qb->addSelect('f.created as created');
$qb->addSelect('f.deleted as deleted');
$qb->addSelect('f.demo as demo');
$qb->addSelect('p.id as defaultProducerId');
$qb = $this->addLot( $qb );
// $qb = $this->countLot( $qb );
// $qb = $this->groupByLot( $qb );
$this->addAddress( $qb );
$qb->addSelect("CONCAT(addr.address,' - ', addr.state, ' - ', addr.city ) as addressFull");
$qb = $this->addOrganization( $qb, $organizationId );
$qb = $this->addAnimal( $qb );
$qb = $this->countAnimal( $qb, $organizationId, $farm );
$qb = $this->addBlackList( $qb );
$qb = $this->addFarm( $qb, $farm );
$qb = $this->addProducer( $qb );
$qb = $this->groupByFarm( $qb );
$qb = $this->countLot( $qb );
$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)
WHERE led.farm = f.id and ed.deleted = 0 AND led.status = 1
) as countEventData'
);
$qb->addSelect('(
SELECT COUNT(distinct t.id)
FROM App\Entity\Treatment t
WHERE t.farm = f.id and t.deleted = 0 and f.organization = o.id
) as countTreatment'
);
$qb->addSelect('(
SELECT COUNT(loc.id)
FROM App\Entity\Location loc
WHERE loc.farm = f.id and loc.deleted = 0
) as countLocation'
);
$this->addCurrentPregnancyStatus( $qb, self::PREGNANTS );
// $qb->addSelect('f.producer as producer');
// $qb->addSelect('f.producers as producers');
// $qb->addSelect('f.locations as locations');
/* CUD timestamps */
$qb->addSelect('f.createdAt as createdAt');
$qb->addSelect('f.updatedAt as updatedAt');
$qb->addSelect('f.deletedAt as deletedAt');
return $qb;
}/*}}}*/
public function entryHelp( $organizationId )
{/*{{{*/
$qb = $this->createQueryBuilder('f');
$qb->andWhere('f.deleted = 0');
$organizationId and $qb->andWhere('f.organization = :organization')
->setParameter('organization', $organizationId);
return $qb;
}/*}}}*/
/* de Logica Ligera Inc */
public function getFarms(User $user)
{/*{{{*/
$qb = $this->createQueryBuilder('f')
->join('f.organization', 'o')
->join('App\Entity\User', 'u', "with", "u.organization = o.id")
->join('App\Entity\Producer', 'p', "with", "p.user = u.id")
->where('f.deleted =:deleted')
->setParameter('deleted', 0);
if (!$user->isAdmin()) {
$qb->andWhere('p.user =:user')
->setParameter('user', $user);
}
$qb->orderBy('f.name', 'ASC');
return $qb->getQuery()->getResult();
}/*}}}*/
public function addDateFilter($filter = [], $qb)
{/*{{{*/
if (!isset($filter['date'])) {
$today = new \DateTime();
$firstEventDay = new \DateTime("2020-01-01");
$dateFilter = [
'month_from' => $firstEventDay->format('m'),
'month_to' => $today->format('m'),
'year_from' => $firstEventDay->format('Y'),
'year_to' => $today->format('Y'),
];
$filter = [
'date' => $dateFilter,
];
}
$dateFrom = new \DateTime($filter['date']['year_from'] . "-" . $filter['date']['month_from'] . "-01");
$dateTo = new \DateTime($filter['date']['year_to'] . "-" . $filter['date']['month_to'] . "-01");
$lastDay = new \DateTime(sprintf('last day of %s', $dateTo->format('Y-m')));
$firstDay = new \DateTime(sprintf('first day of %s', $dateFrom->format('Y-m')));
if (isset($filter['date'])) {
if (isset($filter['date']['month_from']) && isset($filter['date']['year_from'])) {
$qb->andWhere('DATE(a.created) >= :date_from')
->setParameter('date_from', $firstDay);
}
if (isset($filter['date']['month_to']) && isset($filter['date']['year_to'])) {
$qb->andWhere('DATE(a.created) <= :date_to')
->setParameter('date_to', $lastDay);
}
}
return $qb;
}/*}}}*/
public function getFarmAddress( ?Organization $org )
{/*{{{*/
$qb = $this->createQueryBuilder('f');
$qb->select('distinct f.id, f.name, o.name as organizationName, a.latitude, a.longitude, a.address, a.city')
->join('App\Entity\Address', 'a', "with", "a.id = f.address OR a.id is NULL")
->join('App\Entity\Organization', 'o', "with", "o.id = f.organization")
->leftJoin('App\Entity\Lot', 'l', 'with', 'l.farm = f.id OR l.id is NULL')
->where('f.deleted = 0 AND f.createdAt != 0');
$org and $qb->andWhere('o.id = :org')->setParameter('org', $org);
return $qb->getQuery()->getResult();
}/*}}}*/
public function getAllFarmsWithLatAndLngLoaded()
{/*{{{*/
$qb = $this->createQueryBuilder('f');
$qb->select('f.id, f.name,o.name as organizationName, a.latitude,a.longitude,a.address,a.city')
->join('App\Entity\Address', 'a', "with", "a.id = f.address")
->join('App\Entity\Lot', 'l', "with", "f.id = l.farm")
->join('App\Entity\Organization', 'o', "with", "o.id = l.organization")
->where(" a.longitude <> '' AND a.latitude <> '' ")
->andWhere('(f.deleted = 0 OR f.deleted = FALSE)');
return $qb->getQuery()->getResult();
}/*}}}*/
public function getActiveFarmsWithLatAndLngLoaded()
{/*{{{*/
$qb = $this->createQueryBuilder('f');
$qb->select('f.id, f.name,o.name as organizationName, a.latitude,a.longitude,a.address,a.city, COUNT(ed.farm) quantity')
->join('App\Entity\Address', 'a', "with", "a.id = f.address")
->join('App\Entity\EventData', 'ed', "with", "ed.farm = f.id")
->join('App\Entity\Organization', 'o', "with", "o.id = ed.organization")
->where(" a.longitude <> '' AND a.latitude <> '' ")
->andWhere('(f.deleted = 0 OR f.deleted = FALSE)')
->groupBy('f.id, o.name')
->having('quantity >= 20');
return $qb->getQuery()->getResult();
}/*}}}*/
public function getMostRelevantFarms()
{/*{{{*/
$arr = [];
$result = [];
$eventsQuantity = $this->createQueryBuilder('f');
$eventsQuantity->select('f.id, f.name,o.name as organizationName, a.latitude,a.longitude,a.address,a.city, COUNT(ed.farm) eventsQuantity')
->join('App\Entity\Address', 'a', "with", "a.id = f.address")
->join('App\Entity\EventData', 'ed', "with", "ed.farm = f.id")
->join('App\Entity\Organization', 'o', "with", "o.id = ed.organization")
->where(" a.longitude <> '' AND a.latitude <> '' ")
->andWhere('(f.deleted = 0 OR f.deleted = FALSE)')
->andWhere('(ed.deleted = 0 OR ed.deleted = FALSE)')
->groupBy('f.id, o.name')
->orderBy('eventsQuantity', 'DESC')
->setMaxResults(5);
$eventsQuantity = $eventsQuantity->getQuery()->getResult();
$animalsQuantity = $this->createQueryBuilder('f');
$animalsQuantity->select('f.id, COUNT(a.id) animalsQuantity')
->join('App\Entity\Animal', 'a', "with", "a.currentFarm = f.id")
->join('App\Entity\Address', 'a2', "with", "a2.id = f.address")
->where("(a2.longitude <> '' AND a2.latitude <> '')")
->andWhere('(f.deleted = 0 OR f.deleted = FALSE)')
->andWhere('a.deleted = 0 OR a.deleted = FALSE')
->andWhere("a.createdAt >= a.deletedAt OR a.updatedAt >= a.deletedAt OR a.deletedAt is NULL")
->andWhere('a.status = :statusActive')->setParameter('statusActive', Animal::STATUS_ACTIVE)
->andWhere('a.currentLot IS NOT NULL')
->groupBy('f.id')
->orderBy('animalsQuantity', 'DESC')
->setMaxResults(5);
$animalsQuantity = $animalsQuantity->getQuery()->getResult();
foreach ($eventsQuantity as $key => $value) {
foreach ($animalsQuantity as $key2 => $value2) {
if ($value['id'] === $value2['id']) {
$result = array_merge($eventsQuantity[$key], $animalsQuantity[$key2]);
$result = array_push($arr, $result);
}
}
}
return $arr;
}/*}}}*/
public function getFarmByLot(Lot $lot, User $user = null)
{/*{{{*/
$qb = $this->createQueryBuilder('f');
$qb->join('App\Entity\Lot', 'l', "with", "l.farm = f.id")
->where('l.id = :lot')
->setParameter('lot', $lot);
if ($user) {
$qb->andWhere('l.organization = :organization')
->setParameter('organization', $user->getOrganization());
}
$qb->andWhere('l.deleted = 0 OR l.deleted = FALSE');
return $qb->getQuery()->getOneOrNullResult();
}/*}}}*/
public function isDeletable( $farmId ) {
// chequear que la farm tenga organizacion
$qb = $this->createQueryBuilder('f');
$qb->select("f.id");
$this->addOrganization( $qb );
$this->addFarm( $qb, $farmId );
$result = $qb->getQuery()->getResult();
if ( !isset($result[0]["organizationId"]) ) return false;
$organizationId = $result[0]["organizationId"];
// si la tiene, chequear que esta farm sea la unica que pertenezca a la organizacion
$qb = $this->createQueryBuilder('f');
$qb->select("f.id");
$this->addOrganization( $qb, $organizationId );
$this->addFarm( $qb );
$result = $qb->getQuery()->getResult();
if ( sizeof($result) <= 1 ) return false; // si lo es, no se debe eliminar
$qb = $this->createQueryBuilder('f');
$qb->select("f.id");
$this->addLot( $qb );
$this->addFarm( $qb, $farmId );
$qb->andWhere("l.deleted = false AND l.status = true" );
$result = $qb->getQuery()->getResult();
return empty($result); // se podra eliminar si no tiene ningun lote
}
}

src/Repository/FarmRepository.php line 289