ap2i/old/Entity/Network/Repository/NetworkOrganizationRepository.php

<?php

namespace AppBundle\Entity\Network\Repository;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\ResultSetMapping;

/**
 * Description of NetworkOrganizationRepository
 *
 * @author Sébastien Hupin <sebastien.hupin at gmail.com>
 */
class NetworkOrganizationRepository extends EntityRepository
{
  
  public function find($id, $lockMode = null, $lockVersion = null) {
        $query = $this->createQueryBuilder('no')
              ->select('no')
              ->addSelect('n')
              ->addSelect('a')   
              ->join('no.network', 'n')
              ->join('n.image', 'a')   
              ->where('no.id=:id')
              ->setParameter('id', $id)  
        ;
        return $query->getQuery()->getOneOrNullResult();
  }  

  public function findOrganizations($id, \DateTime $startEffectiveDate = null,
          \DateTime $endEffectiveDate = null, \DateTime $startExitDate = null,
          \DateTime $endExitDate = null, $leadingCause = null, $draft = 0,
          array $orderBy = null, $limit = null, $offset = null)
  {

    $organizations = array();

    $whereClause = array('n.network_id = :network_id', 'n.draft = :draft');
    $parameters = array('network_id' => $id, 'draft'=>$draft);    
    $orderBy = array_merge((array) $orderBy, array(
        'startDate' => 'ASC'));
    $limit = array_merge((array) $limit, (array) $offset);
    
    if (null !== $startEffectiveDate) {
      $whereClause[] = 'n.startDate >= :startEffectiveDate';
      $parameters['startEffectiveDate'] = $startEffectiveDate->format('Y-m-d');
    }

    if (null !== $endEffectiveDate) {
      $whereClause[] = 'n.startDate <= :endEffectiveDate';
      $parameters['endEffectiveDate'] = $endEffectiveDate->format('Y-m-d');
    }

    if (null !== $startExitDate) {
      $whereClause[] = 'n.endDate >= :startExitDate';
      $parameters['startExitDate'] = $startExitDate->format('Y-m-d');
    }

    if (null !== $endExitDate) {
      $whereClause[] = 'n.endDate <= :endExitDate';
      $parameters['endExitDate'] = $endExitDate->format('Y-m-d');
    }

    if (null !== $leadingCause) {
      $leadingCauseEnum = new \AppBundle\Enum\Network\LeadingCauseEnum($leadingCause);
      $whereClause[] = 'n.leadingCause = :leadingCause';
      $parameters['leadingCause'] = $leadingCauseEnum->getValue();
    }    
    
    $sql = sprintf("
      SELECT o.id AS id, o.name AS name
      FROM Organization AS o
      INNER JOIN NetworkOrganization AS n ON n.organization_id = o.id
      WHERE %s
      ORDER BY %s
      %s
    ", implode(' AND ', $whereClause), 
       implode(',', array_map(
                      function($k, $v) {
                              return "$k $v";
                      },
                      array_keys($orderBy), array_values($orderBy)
                    )
       ),
       (2 === count($limit)) ? sprintf('LIMIT %s', implode(',',$limit)) : ''
    );

    $rsm = new ResultSetMapping();
    $rsm->addScalarResult('id', 'id', 'integer');
    $rsm->addScalarResult('name', 'name');

    $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
    $query->setParameters($parameters);

    $result = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

    foreach ($result as $o) {
      $organization = new \AppBundle\Entity\Organization\Organization();
      $organization->setId($o['id']);
      $organization->setName($o['name']);
      $organizations[] = $organization;
    }

    return $organizations;
  }
  
  /**
   * Check if the identifier exist
   * 
   * @param string $networkName
   * @param string $identifier
   * @return boolean
   */
  public function checkUniqIdentifier($networkName, $identifier) {
      $query = $this->createQueryBuilder('no')
            ->select('no')  
            ->join('no.network', 'n')
            ->join('no.organization', 'o') 
            ->where('n.name=:name')
            ->andWhere('o.identifier=:identifier')
            ->setParameter('name', $networkName)
            ->setParameter('identifier', $identifier)  
      ;

      return $query->getQuery()->getOneOrNullResult() ? false : true;
  }
  
}