ap2i/old/Entity/AccessAndFunction/Repository/AccessRepository.php

<?php

namespace AppBundle\Entity\AccessAndFunction\Repository;
use AppBundle\Entity\AccessAndFunction\Access;
use AppBundle\Entity\AccessAndFunction\FunctionType;
use AppBundle\Entity\HasItemQueryBuilderInterface;
use AppBundle\Entity\Organization\Organization;
use AppBundle\Entity\Person\Person;
use AppBundle\Enum\AccessAndFunction\FunctionEnum;
use AppBundle\Enum\AccessAndFunction\LicenseTypeEnum;
use AppBundle\Enum\OnlineRegistration\ValidationStateEnum;
use AppBundle\Services\AccessService;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\QueryBuilder;

/**
 * Description of AccessRepository
 *
 * @author Sébastien Hupin <sebastien.hupin at gmail.com>
 */
class AccessRepository extends EntityRepository implements HasItemQueryBuilderInterface
{

    /**
     * @param Person $person
     * @param bool $ignoreCheckNetworkOrganizationEndDate
     * @return array
     * @throws \Exception
     */
    public function findValidAccess(\AppBundle\Entity\Person\Person $person, $ignoreCheckNetworkOrganizationEndDate = FALSE, $controleLoginEnabled = true)
    {
        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);

        $datetime = new \DateTime();
        $today = $datetime->format('Y-m-d');

        $qb = $this->createQueryBuilder('a');

        $qb
            ->innerJoin('AppBundle:Organization\Organization', 'o', 'WITH', 'o.id = a.organization')
            ->innerJoin('AppBundle:Network\NetworkOrganization', 'n', 'WITH', 'o.id = n.organization')
            ->where('a.person = :person')
            ->andWhere('n.startDate <= :today')
            ->setParameter('person', $person)
            ->setParameter('today', $today)
           ;

        if($controleLoginEnabled){
            $qb
                ->andWhere('a.loginEnabled = :loginEnabled')
                ->setParameter('loginEnabled', true);
        }

        if ($ignoreCheckNetworkOrganizationEndDate == FALSE) {
            $qb
               ->andWhere('n.endDate >= :today OR n.endDate = :emptydate OR n.endDate IS NULL')
               ->setParameter('emptydate', '0000-00-00');
        }

        $result = $qb->getQuery()->getResult();
        $filter->setParameter('disabledFilter', 0);
        return $result;

    }

    /**
     * @param AccessService $access
     * @return array
     */
    public function findValidTeacher(AccessService $access)
    {
        $qb = $this->createQueryBuilder('a');

        $qb->innerJoin('a.person', 'person')
            ->innerJoin('AppBundle:AccessAndFunction\OrganizationFunction', 'o', 'WITH', 'o.access = a.id')
            ->innerJoin('AppBundle:AccessAndFunction\FunctionType', 'ft', 'WITH', 'o.functionType = ft.id')
            ->where('ft.mission = :mission')
            ->andWhere('a.organization = :organization')
            ->setParameter('organization', $access->getOrganization())
            ->setParameter('mission', FunctionEnum::TEACHER)
            ->orderBy('person.name')
            ;

        return $qb->getQuery()->getResult();

    }

    /**
     * @param Access $access
     * @return array
     */
    public function doesHaveFunction($accessId, $function)
    {
        $qb = $this->createQueryBuilder('a');
        $qb->innerJoin('a.organizationFunction', 'o')
            ->innerJoin('o.functionType', 'ft')
            ->where('ft.mission = :mission')
            ->andWhere('a.id = :id')
            ->setParameter('id', $accessId)
            ->setParameter('mission', $function)
        ;
        return count($qb->getQuery()->getResult()) > 0;
    }

    /**
     * @param Access $access
     * @return array
     */
    public function doesHeStudentDuringPeriod(Access $access, $startDate, $enDate)
    {
        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);
        $qb = $this->createQueryBuilder('a');

        $qb->innerJoin('a.organizationFunction', 'o')
            ->innerJoin('o.functionType', 'ft')
            ->where('ft.mission = :mission')
            ->andWhere('(o.startDate <= :enDate AND (o.endDate >= :startDate  OR  o.endDate IS NULL)) AND ((o.endDate >= :startDate  OR  o.endDate IS NULL) AND o.startDate <= :enDate)')
            ->andWhere('a.id = :id')
            ->setParameter('id', $access->getId())
            ->setParameter('mission', FunctionEnum::STUDENT)
            ->setParameter('startDate', $startDate)
            ->setParameter('enDate', $enDate);
        ;

        $filter->setParameter('disabledFilter', 0);
        return count($qb->getQuery()->getResult()) > 0;
    }

    /**
     * Used by Elastica to transform results to model
     *
     * @param string $entityAlias
     * @return  Doctrine\ORM\QueryBuilder
     */
    public function createSearchQueryBuilder($entityAlias)
    {
        $qb = $this->createQueryBuilder($entityAlias);

        $qb->select($entityAlias, 'p', 'g')
            ->innerJoin($entityAlias.'.person', 'p')
//            ->leftJoin('p.addressPostal', 'a1', 'WITH', "a1.person = p.id AND a1.type = 'ADDRESS_PRINCIPAL'")
//            ->leftJoin('p.guardians', 'g', 'WITH', 'g.children = p.id  AND g.isPrincipal = 1')
//            ->leftJoin(
//                'g.children',
//                'p2',
//                'WITH',
//                'p2.id = g.children'
//            )//            ->leftJoin('p2.addressPostal', 'a2', 'WITH', "a2.person = p2.id AND a2.type = 'ADDRESS_PRINCIPAL'")
        ;

        return $qb;
    }

    public function findDuplicatePhysicalAcces($name,$givenName, Organization $organization)
    {
        $qb = $this->createQueryBuilder('a')
            ->innerJoin(Person::class, 'pers', 'WITH', 'a.person= pers.id')
            ->andWhere('pers.isPhysical = 1')
            ->andWhere('pers.givenName=:givenName')
            ->andWhere('pers.name=:name')
            ->andWhere('a.organization=:organizationId')
            ->setParameter(':givenName', $givenName)
            ->setParameter(':name', $name)
            ->setParameter(':organizationId', $organization);

        return $qb->getQuery()->getResult();
    }

    public function findDuplicateMoralAcces(Person $person, Organization $organization)
    {
        $qb = $this->createQueryBuilder('a')
            ->innerJoin(Person::class, 'pers', 'WITH', 'a.person= pers.id')
            ->andWhere('pers.isPhysical = 0')
            ->andWhere('pers.name=:name')
            ->andWhere('a.organization=:organizationId')
            ->setParameter(':name', $person->getName())
            ->setParameter(':organizationId', $organization);

        return $qb->getQuery()->getResult();
    }

    public function createDetailQueryBuilder(array $groups = null): QueryBuilder
    {
        if(is_array($groups) && in_array('access_details', $groups)){
            return $this
                ->createQueryBuilder('o')
                ->select('o', 'pe', 'pap', 'af')
                ->leftJoin('o.person', 'pe')
                ->leftJoin('pe.personAddressPostal', 'pap')
                ->leftJoin('o.accessFamily', 'af')
                ;
        }

        return $this->createQueryBuilder('o');
    }

    public function createListQueryBuilder(){
        return $this
            ->createQueryBuilder('o')
            ->select('o', 'person')
            ->innerJoin('o.person', 'person')
            ;
    }

    /**
     * @param Organization $organization
     * @param $mission
     * @return array
     */
    public function findByOrganizationAndMission(Organization $organization,$mission)
    {
        $datetime = new \DateTime();
        $today = $datetime->format('Y-m-d');


        $qb = $this->createQueryBuilder('a')
            ->innerJoin(Person::class, 'pers', 'WITH', 'a.person= pers.id')
            ->andWhere('pers.isPhysical = 1')
            ->andWhere('a.organization=:organizationId')
            ->innerJoin('a.organizationFunction', 'ofu')
            ->innerJoin('ofu.functionType', 'ft')
            ->andWhere('ft.mission=:mission')
            ->andWhere('ofu.startDate < :today AND (ofu.endDate >= :today OR  ofu.endDate IS NULL)')
            ->setParameter('organizationId', $organization)
            ->setParameter('mission', $mission)
            ->setParameter('today', $today);

        return $qb->getQuery()->getResult();
    }

    /**
     * Retourne tous les accesses possédant une fonctions données d'une organisation
     * @param $organizationId
     * @return array
     */
    public function findAllAccessesByMission($organizationId, array $functions) {

        $queryBuilder =
            $this->createQueryBuilder('access');

        $queryBuilder
            ->select('access, access_wishes, access_billing, access_network_setting, access_communication, access_social')
            ->innerJoin('access.organization', 'organization')
            ->innerJoin('access.organizationFunction', 'ofu')
            ->innerJoin('ofu.functionType', 'ft')
            ->leftJoin('access.accessWishes', 'access_wishes')
            ->leftJoin('access.accessBilling', 'access_billing')
            ->leftJoin('access.accessNetworkSetting', 'access_network_setting')
            ->leftJoin('access.accessCommunication', 'access_communication')
            ->leftJoin('access.accessSocial', 'access_social')
            ->andWhere('ft.mission in (:mission)')
            ->andWhere('organization.id=:organizationId')
            ->andWhere('access.newAccess=:newAccess')
            ->setParameter('newAccess',  false)
            ->setParameter('organizationId',  $organizationId)
            ->setParameter('mission',  $functions);

        $query = $queryBuilder->getQuery();
        $results = $query->getResult();

        return $results;
    }

    /**
     * Retourne tous les accesses possédant une fonctions données d'une organisation
     * @param $organizationId
     * @return array
     */
    public function findAllAccessesByMissionAndWithoutCompletingRegistration($organizationId, array $functions) {

        $queryBuilder =
            $this->createQueryBuilder('access');

        $queryBuilder
            ->select('access, access_wishes, access_billing, access_network_setting, access_communication, access_social,access_wish','access_family_wish')
            ->innerJoin('access.organization', 'organization')
            ->innerJoin('access.organizationFunction', 'ofu')
            ->innerJoin('ofu.functionType', 'ft')
            ->leftJoin('access.accessWishes', 'access_wishes')
            ->leftJoin('access.accessBilling', 'access_billing')
            ->leftJoin('access.accessNetworkSetting', 'access_network_setting')
            ->leftJoin('access.accessCommunication', 'access_communication')
            ->leftJoin('access.accessSocial', 'access_social')
            ->leftJoin('access.accessWishes', 'access_wish')
            ->leftJoin('access_wish.accessFamilyWish', 'access_family_wish')
            ->andWhere('ft.mission in (:mission)')
            ->andWhere('organization.id=:organizationId')
            ->andWhere('access.newAccess=:newAccess')
            ->andWhere('access_wish.id is NULL OR access_family_wish.registrationCompleted = 0')
            ->setParameter('newAccess',  false)
            ->setParameter('organizationId',  $organizationId)
            ->setParameter('mission',  $functions);

        $query = $queryBuilder->getQuery();
        $results = $query->getResult();

        return $results;
    }

    public function findAllAccessesByAccessWishOpenForNewEducation($organizationId) {
        $currentYear = (new \DateTime())->format('Y');

        $queryBuilder =
            $this->createQueryBuilder('access');

        $queryBuilder
            ->select('access')
            ->innerJoin('access.organization', 'organization')
            ->leftJoin('access.accessWishes', 'access_wishes')
            ->andWhere('access_wishes.validationState =:validationState')
            ->andWhere('organization.id=:organizationId')
            ->andWhere('(access_wishes.createDate >= :startDate and access_wishes.createDate <= :endDate)')
            ->setParameter('organizationId',  $organizationId)
            ->setParameter('validationState',  ValidationStateEnum::OPEN_FOR_EDUCATIONS)
            ->setParameter('startDate', new \DateTime($currentYear.'-01-01'))
            ->setParameter('endDate', new \DateTime($currentYear.'-12-31'))
            ;

        $query = $queryBuilder->getQuery();
        $results = $query->getResult();

        return $results;
    }

    /**
     * Get all students for a teacher
     * @param Access $teacher
     * @return Array<Access>
     */
    public function findStudent(Access $teacher) {

        $queryBuilder =
            $this->createQueryBuilder('o');

        $queryBuilder->innerJoin('o.educationStudent', 'es')
            ->innerJoin('es.teachers', 'teacher')
            ->andWhere($queryBuilder->expr()->eq('teacher.id', ':teacher'))
            ->setParameter(':teacher', $teacher);

        $query = $queryBuilder->getQuery();
        $results = $query->getResult();

        return $results;

    }

    /**
     * @param Organization $organization
     * @param $startDate
     * @param $enDate
     * @return array
     */
    public function findByStudentsByDates(Organization $organization,$startDate,$enDate)
    {
        $qb = $this->createQueryBuilder('a')
            ->innerJoin(Person::class, 'pers', 'WITH', 'a.person= pers.id')
            ->andWhere('pers.isPhysical = 1')
            ->andWhere('a.organization=:organizationId')
            ->innerJoin('a.organizationFunction', 'ofu')
            ->innerJoin('ofu.functionType', 'ft')
            ->andWhere('ft.mission=:mission')
            ->andWhere('(ofu.startDate <= :enDate AND (ofu.endDate >= :startDate  OR  ofu.endDate IS NULL)) AND ((ofu.endDate >= :startDate  OR  ofu.endDate IS NULL) AND ofu.startDate <= :enDate)')
            ->setParameter('organizationId', $organization)
            ->setParameter('mission', FunctionEnum::STUDENT)
            ->setParameter('startDate', $startDate)
            ->setParameter('enDate', $enDate);

        return $qb->getQuery()->getResult();
    }

    /**
     * @param $accessIds
     * @param $organization
     * @return array
     */
    public function findAccessByIds($accessIds,$organization)
    {
        $queryBuilder = $this->createQueryBuilder('o');
        $queryBuilder
            ->andWhere(
                $queryBuilder->expr()->in('o.id', ':ids'))
            ->andWhere('o.organization=:organizationId')
            ->setParameter('ids', $accessIds)
            ->setParameter('organizationId', $organization);

        return $queryBuilder->getQuery()->getResult();
    }

    public function findAccessWhoAreReEnrolled(Organization $organization){
        $datetimeNow = new \Datetime('now');
        $currentYear = $datetimeNow->format('Y');
        $pastYear = $currentYear-1;

        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);
        $queryBuilder = $this->createQueryBuilder('o');
        $queryBuilder
            ->innerJoin('o.educationStudent', 'es')
            ->andWhere('es.startYear=:pastYear')
            ->innerJoin('o.educationStudent', 'es2')
            ->andWhere('es2.startYear=:currentYear')
            ->andWhere('o.organization=:organizationId');
        $queryBuilder
            ->setParameter('pastYear', $pastYear)
            ->setParameter('currentYear', $currentYear)
            ->setParameter('organizationId', $organization)

            ->groupBy('o.id');

        $result = $queryBuilder->getQuery()->getResult();
        $filter->setParameter('disabledFilter', 0);
        return $result;
    }

    public function findAccessWhoAreEnrolled(Organization $organization){
        $datetimeNow = new \Datetime('now');
        $currentYear = $datetimeNow->format('Y');
        $pastYear = $currentYear-1;

        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);
        $queryBuilder = $this->createQueryBuilder('o');
        $queryBuilder
            ->innerJoin('o.educationStudent', 'es2')
            ->andWhere('es2.startYear=:currentYear')
            ->andWhere('o.organization=:organizationId');
        $queryBuilder
            ->setParameter('currentYear', $currentYear)
            ->setParameter('organizationId', $organization)

            ->groupBy('o.id');

        $result = $queryBuilder->getQuery()->getResult();
        $filter->setParameter('disabledFilter', 0);
        return $result;
    }
}