query for contacts with board forum access

[civicrm-scripts.git] / board-forum-contacts.sql

-- SPDX-FileCopyrightText: 2023 Andrew Engelbrecht
--
-- SPDX-License-Identifier: GPL-3.0-or-later

select c.id from civicrm_contact c
where c.is_deleted = 0
and c.id not in (
    select contact_id from civicrm_group_contact
    where id = 1978 and status = 'Added' -- adhoc exclusion group
) and (
    c.id in (
        select contact_id from civicrm_group_contact
        where id = 1977 and status = 'Added' -- adhoc inclusion group
    ) or (
        c.id in (
            select contact_id from civicrm_membership
            where status_id in (1, 2, 3) -- is a member: new, current or grace
        ) and (
            c.id in (
                select contact_id from civicrm_membership
                where status_id in (1, 2, 3, 4) and end_date >= '2018-01-19' and join_date <= '2022-10-19' -- was a member during time period
            ) or c.id in (
                select contact_id from civicrm_contribution
                where contribution_status_id = 1 and receive_date >= '2018-01-19' and receive_date <= '2022-10-19'
                group by contact_id
                having sum(total_amount) >= '120.00' -- donated $120 or more during time period
            )
        )
    )
) order by id asc
into outfile '/var/lib/mysql-files/board-forum-contacts4.csv' fields terminated by ','
;