accountssystemsreport

WITH org_admins AS (

         SELECT accountgroup.mgm_id,

            accountgroup.account_id

           FROM accountgroup

          WHERE ((accountgroup.account_group_type_label)::text = 'org_admin'::text)

        ), system_users AS (

         SELECT true AS is_admin,

            account.mgm_id,

            account.account_id,

            system.system_id,

            NULL::character varying AS group_name

           FROM (system

             JOIN account ON (((system.mgm_id = account.mgm_id) AND ((system.organization)::text = (account.organization)::text))))

        UNION

         SELECT false AS is_admin,

            systemgrouppermission.mgm_id,

            systemgrouppermission.account_id,

            systemgroupmember.system_id,

            systemgrouppermission.group_name

           FROM (systemgrouppermission

             JOIN systemgroupmember ON (((systemgrouppermission.mgm_id = systemgroupmember.mgm_id) AND (systemgrouppermission.system_group_id = systemgroupmember.system_group_id))))

        ), users_details AS (

         SELECT account.mgm_id,

            account.account_id,

            account.username,

            account.organization,

            (org_admins.account_id IS NOT NULL) AS is_admin,

            account.synced_date

           FROM (account

             LEFT JOIN org_admins ON (((account.mgm_id = org_admins.mgm_id) AND (account.account_id = org_admins.account_id))))

        )

 SELECT users_details.mgm_id,

    users_details.account_id,

    users_details.username,

    users_details.organization,

    system_users.system_id,

    system_users.group_name,

    users_details.is_admin,

    users_details.synced_date

   FROM (users_details

     LEFT JOIN system_users ON (((users_details.mgm_id = system_users.mgm_id) AND (users_details.is_admin = system_users.is_admin) AND (users_details.account_id = system_users.account_id))))

  WHERE (system_users.system_id IS NOT NULL)

  ORDER BY users_details.mgm_id, users_details.account_id, system_users.system_id;