mucho.sql

-- include: party,person,org,org.subunits,user
-- filter: party-id,party-uuid,person-identifier,organization-identifier,user-id,multiple

WITH uuids_by_party_uuid AS (
    SELECT party."uuid", party.version_id
    FROM register.party AS party
    WHERE party."uuid" = ANY (@partyUuids)
),
uuids_by_party_id AS (
    SELECT party."uuid", party.version_id
    FROM register.party AS party
    WHERE party."id" = ANY (@partyIds)
),
uuids_by_person_identifier AS (
    SELECT party."uuid", party.version_id
    FROM register.party AS party
    WHERE party."person_identifier" = ANY (@personIdentifiers)
),
uuids_by_organization_identifier AS (
    SELECT party."uuid", party.version_id
    FROM register.party AS party
    WHERE party."organization_identifier" = ANY (@organizationIdentifiers)
),
uuids_by_user_id AS (
    SELECT "user"."uuid", party.version_id
    FROM register."user" AS "user"
    INNER JOIN register.party AS party USING (uuid)
    WHERE "user".user_id = ANY (@userIds)
),
top_level_uuids AS (
    SELECT "uuid", version_id FROM uuids_by_party_uuid
    UNION
    SELECT "uuid", version_id FROM uuids_by_party_id
    UNION
    SELECT "uuid", version_id FROM uuids_by_person_identifier
    UNION
    SELECT "uuid", version_id FROM uuids_by_organization_identifier
    UNION
    SELECT "uuid", version_id FROM uuids_by_user_id
),
filtered_users AS (
    SELECT "user".*
    FROM register."user" AS "user"
    WHERE "user".is_active
       OR "user".user_id = ANY (@userIds)
),
sub_units AS (
    SELECT
        parent."uuid" AS parent_uuid,
        parent.version_id AS parent_version_id,
        ra."from_party" AS child_uuid
    FROM top_level_uuids AS parent
    JOIN register.external_role_assignment ra
         ON ra.to_party = parent."uuid"
        AND ra.source = 'ccr'
        AND (ra.identifier = 'ikke-naeringsdrivende-hovedenhet' OR ra.identifier = 'hovedenhet')
),
uuids AS (
    SELECT
        "uuid" AS "uuid",
        NULL::uuid AS parent_uuid,
        version_id AS sort_first,
        NULL::uuid AS sort_second
    FROM top_level_uuids
    UNION
    SELECT 
        child_uuid AS "uuid",
        parent_uuid,
        parent_version_id AS sort_first,
        child_uuid AS sort_second
    FROM sub_units
)
SELECT
    uuids.parent_uuid p_parent_uuid,
    party.uuid p_uuid,
    party.id p_id,
    party.party_type p_party_type,
    party.display_name p_display_name,
    party.person_identifier p_person_identifier,
    party.organization_identifier p_organization_identifier,
    party.created p_created,
    party.updated p_updated,
    party.is_deleted p_is_deleted,
    party.version_id p_version_id,
    person.first_name p_first_name,
    person.middle_name p_middle_name,
    person.last_name p_last_name,
    person.short_name p_short_name,
    person.date_of_birth p_date_of_birth,
    person.date_of_death p_date_of_death,
    person.address p_address,
    person.mailing_address p_person_mailing_address,
    org.unit_status p_unit_status,
    org.unit_type p_unit_type,
    org.telephone_number p_telephone_number,
    org.mobile_number p_mobile_number,
    org.fax_number p_fax_number,
    org.email_address p_email_address,
    org.internet_address p_internet_address,
    org.mailing_address p_org_mailing_address,
    org.business_address p_business_address,
    "user".is_active u_is_active,
    "user".user_id u_user_id
FROM uuids AS uuids
INNER JOIN register.party AS party USING (uuid)
LEFT JOIN register.person AS person USING (uuid)
LEFT JOIN register.organization AS org USING (uuid)
LEFT JOIN filtered_users AS "user" USING (uuid)
ORDER BY
    uuids.sort_first,
    uuids.sort_second NULLS FIRST,
    "user".is_active DESC,
    "user".user_id DESC