query for contacts with board forum access

[civicrm-scripts.git] / board-forum-contacts-on-join.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 not 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-contacts-on-join2.csv' fields terminated by ','
;