ap2i/old/Entity/Organization/Repository/OrganizationRepository.php

<?php


namespace AppBundle\Entity\Organization\Repository;

use AppBundle\Validator\AccessAndFunction\OrganizationFunction;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
use Doctrine\ORM\Query\ResultSetMapping;
use AppBundle\Enum\Organization\principalTypeEnum;
use PDO;

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

    public function getCurrentsNetworkId($access)
    {
        $filter = $this->_em->getFilters()->enable('activity_year');
        $filter->setParameter('disabledFilter', 1);

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

        $queryBuilder
            ->select("network.id")
            ->innerJoin('o.network', 'networkOrga')
            ->innerJoin('networkOrga.network', 'network')
            ->where('o.id = :organizationId')
            ->andWhere(
                $queryBuilder->expr()->orX(
                    $queryBuilder->expr()->gte('networkOrga.endDate', ':today'),
                    $queryBuilder->expr()->isNull('networkOrga.endDate')
                )
            )
            ->setParameter('organizationId', $access->getOrganization()->getId())
            ->setParameter('today', new \DateTime('now'));


        $query = $queryBuilder->getQuery();
        $netWorks = $query->getOneOrNullResult();

        $filter->setParameter('disabledFilter', 0);

        return $netWorks;
    }

    /**
     * Finds all parent entities in the repository.
     *
     * @param integer $id The identifier.
     *
     * @return array The organizations.
     */
    public function findParents($id)
    {
        $sql = sprintf("
      SELECT *
      FROM Organization o
      JOIN
        (SELECT 
          @parents:=(
            SELECT GROUP_CONCAT(parent_id)
            FROM NetworkOrganization
            WHERE organization_id = %d
          )
        ) tmp
      WHERE FIND_IN_SET(o.id, @parents)
    ", (int)$id);

        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata('AppBundle:Organization\Organization', 'o');
        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);

        return $query->getResult();
    }

    /**
     * Finds recursively all parent entities in the repository.
     *
     * @param integer $id The identifier.
     *
     * @return array The organizations.
     */
    public function findAllParents($id)
    {

        $sql = sprintf("
      SELECT *
     FROM Organization o
     JOIN
       (
         SELECT
                 GROUP_CONCAT(@target:=
                     (
                         SELECT @source:=GROUP_CONCAT(parent_id)
                         FROM
                             NetworkOrganization
                         WHERE
                             FIND_IN_SET(organization_id, @source) AND parent_id NOT IN (32366, 13)
                     )
                 ) AS parents
         FROM
             (SELECT
                 @source:=%d,
                 @target:=0
             ) vars,
             NetworkOrganization
         WHERE
             @source IS NOT NULL
       ) tmp
       WHERE FIND_IN_SET(o.id, parents)
    ", (int)$id);

        $rsm = new ResultSetMappingBuilder($this->getEntityManager());
        $rsm->addRootEntityFromClassMetadata('AppBundle:Organization\Organization', 'o');

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

        return $query->getResult();
    }

    /**
     * Check if this organization is a last parent
     *
     * @param integer $id The identifier.
     *
     * @return array The organizations.
     */
    public function isLastParent($id)
    {

        $sql = sprintf("
        SELECT 
            IF( (SELECT o.id 
                    FROM Organization as o 
                    WHERE o.id=neto.organization_id
                    AND o.principalType IN('%s','%s','%s','%s','%s','%s')) IS NOT NULL ,0,1) AS is_last_parent
        FROM
            NetworkOrganization as neto
        WHERE
            neto.parent_id = %d
            and (neto.endDate is null or neto.endDate = \"0000-00-00\" or neto.endDate > CURDATE())
        GROUP BY is_last_parent
        ORDER BY is_last_parent DESC", principalTypeEnum::NATIONAL_FEDERATION, principalTypeEnum::REGIONAL_FEDERATION, principalTypeEnum::LOCAL_FEDERATION, principalTypeEnum::GROUPMENT, principalTypeEnum::DEPARTEMENTAL_FEDERATION, principalTypeEnum::DELEGATION, (int)$id);

        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('is_last_parent', 'is_last_parent', 'integer');
        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
        $result = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

        foreach ($result as $a_tmp) {
            if ($a_tmp['is_last_parent']) {
                return true;
            }
        }

        return false;
    }

    /**
     * Route optimisée pour retourner l'ensemble des structures d'une fédération au format json
     *
     * Cette route est utilisée par l'iframe de recherche des structures
     * @see https://gitlab.2iopenservice.com/opentalent/frames
     */
    public function getDataByFederationId($parentId)
    {

        // NOTE: Cette route est utilisée pour l'affichage et la recherche des structures adhérentes à une fédération
        // Pour éviter une requête récursive et conserver des performances correctes, on a mis en place ces JOIN chainés.
        // Au moment du développement de cette route (juin 2021), aucune structure n'a plus de 4 fédération parentes,
        // les 5 niveaux de JOIN devraient donc suffire.
        $sql = "SELECT o.id, o.name, o.logo_id as logoId, o.principalType, p.otherWebsite as website,    
                        GROUP_CONCAT(COLUMN_JSON(COLUMN_CREATE(
                             'type', oa.type, 'latitude', a.latitude, 'longitude', a.longitude, 
                             'streetAddress', TRIM(BOTH '\n' FROM CONCAT_WS('\n', a.streetAddress, a.streetAddressSecond, a.streetAddressThird)),
                             'postalCode', a.postalCode, 'addressCity', a.addressCity, 'country', c.name))) as addresses,
                        (SELECT CONCAT(GROUP_CONCAT(DISTINCT CONCAT(tp.name)))
                        FROM organization_type_of_practices AS otp
                        LEFT JOIN TypeOfPractice AS tp ON(tp.id = otp.typeofpractice_id)
                        WHERE otp.organization_id = o.id) AS practices,
                       n1.parent_id as n1Id, net1.name as n1Name, n2.parent_id as n2Id, n3.parent_id as n3Id, n4.parent_id as n4Id, n5.parent_id as n5Id,
                       CONCAT_WS(',', n1.parent_id, n2.parent_id, n3.parent_id, n4.parent_id, n5.parent_id) as parents
                    FROM opentalent.Organization o
                        INNER JOIN opentalent.Parameters p on o.parameters_id = p.id
                        LEFT JOIN opentalent.OrganizationAddressPostal oa on oa.organization_id = o.id
                        LEFT JOIN opentalent.AddressPostal a on oa.addressPostal_id = a.id
                        LEFT JOIN opentalent.Country c ON (c.id = a.addressCountry_id)
                        INNER JOIN (SELECT DISTINCT organization_id, parent_id FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n1 on n1.organization_id = o.id
                        INNER JOIN Organization net1 ON net1.id = n1.parent_id
                        LEFT JOIN (SELECT DISTINCT organization_id, parent_id FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n2 on n2.organization_id = n1.parent_id
                        LEFT JOIN (SELECT DISTINCT organization_id, parent_id FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n3 on n3.organization_id = n2.parent_id
                        LEFT JOIN (SELECT DISTINCT organization_id, parent_id FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n4 on n4.organization_id = n3.parent_id
                        LEFT JOIN (SELECT DISTINCT organization_id, parent_id FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n5 on n5.organization_id = n4.parent_id
                        WHERE :parentId IN (n1.parent_id, n2.parent_id, n3.parent_id, n4.parent_id, n5.parent_id)
                        GROUP BY o.id
        ;";

        $em = $this->getEntityManager();
        $stmt = $em->getConnection()->prepare($sql);
        $stmt->execute(['parentId' => $parentId]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Route optimisée pour retourner les données de réseau d'une structure membre de fédération, au format json
     *
     * Cette route est utilisée par l'iframe de recherche des structures
     * @see https://gitlab.2iopenservice.com/opentalent/frames
     */
    public function getDataByOrganizationId($organizationId)
    {

        $sql = "SELECT o.id, o.name, o.logo_id as logoId, o.description, o.image_id as imageId, p.otherWebsite as website, a.latitude, a.longitude,
                       GROUP_CONCAT(COLUMN_JSON(COLUMN_CREATE(
                             'type', oa.type, 'latitude', a.latitude, 'longitude', a.longitude, 
                             'streetAddress', TRIM(BOTH '\n' FROM CONCAT_WS('\n', a.addressOwner, a.streetAddress, a.streetAddressSecond, a.streetAddressThird)),
                             'postalCode', a.postalCode, 'addressCity', a.addressCity, 'country', c.name))) as addresses,
                       cp.telphone, cp.mobilPhone, cp.email, o.facebook, o.twitter, o.instagram, o.youtube,
                       (SELECT CONCAT(GROUP_CONCAT(DISTINCT CONCAT(tp.name)))
                        FROM organization_type_of_practices AS otp
                        LEFT JOIN TypeOfPractice AS tp ON(tp.id = otp.typeofpractice_id)
                        WHERE otp.organization_id = o.id) AS practices,
                       oar.articles,
                       n1.parent_id as n1Id, net1.name as n1Name
                    FROM opentalent.Organization o
                        INNER JOIN opentalent.Parameters p on o.parameters_id = p.id
                        LEFT JOIN opentalent.OrganizationAddressPostal oa on oa.organization_id = o.id
                        LEFT JOIN opentalent.AddressPostal a on oa.addressPostal_id = a.id
                        LEFT JOIN opentalent.Country c ON c.id = a.addressCountry_id
                        INNER JOIN (SELECT * FROM NetworkOrganization WHERE parent_id NOT IN (32366, 13) AND (endDate IS NULL OR endDate = '0000-00-00')) n1 on n1.organization_id = o.id
                        INNER JOIN Organization net1 ON net1.id = n1.parent_id
                        LEFT JOIN opentalent.organization_contactpoint ocp ON ocp.organization_id = o.id
                        INNER JOIN (SELECT * FROM opentalent.ContactPoint WHERE `contactType`='PRINCIPAL') cp ON cp.id = ocp.contactPoint_id
                        LEFT JOIN (
                            SELECT oar_.organization_id, GROUP_CONCAT(COLUMN_JSON(COLUMN_CREATE('id', oar_.id, 'title', oar_.title, 'date', DATE_FORMAT(oar_.date, '%Y-%m-%dT%TZ'), 'link', oar_.link))) as articles
                            FROM (SELECT * FROM OrganizationArticle WHERE link is not null and link != '' ORDER BY date DESC) as oar_
                            group by organization_id
                        ) oar ON oar.organization_id = o.id
                    WHERE o.id = :organizationId;";

        $em = $this->getEntityManager();
        $stmt = $em->getConnection()->prepare($sql);
        $stmt->execute(['organizationId' => $organizationId]);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}