ap2i/sql/schema-extensions/002-view_federation_structures.sql

CREATE OR REPLACE VIEW view_federation_structures
AS
    SELECT o.id, o.name, o.logo_id as logoId, o.description, o.image_id as imageId, o.principalType as type,
           o.portailVisibility,
           IF(p.website is not null AND p.website != '', p.website, CONCAT('https://', p.subDomain, '.opentalent.fr')) AS website,
           CONCAT('[', 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,
           cp.telphone as phone, cp.mobilPhone as mobilePhone, 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 parentId, net1.name as parentName,
           CONCAT_WS(',', n1.parent_id, n2.parent_id, n3.parent_id, n4.parent_id, n5.parent_id) as parents,
           CONCAT('[', GROUP_CONCAT(DISTINCT(COLUMN_JSON(COLUMN_CREATE('id', ac.id,'name', ac.name, 'description', ac.description, 'imageId', ac.imageActivity_id)))), ']') as sections
    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)
             LEFT JOIN opentalent.Activity ac ON (ac.organization_id = o.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, CONCAT('[', 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
             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
    GROUP BY o.id;