Inserting active, enroled, program participants into the corresponding program community website.

gistfile1.sql

INSERT INTO `community_members` SELECT NULL, a.`community_id`, e.`proxy_id`, 1, UNIX_TIMESTAMP(), 0
FROM `communities` AS a
JOIN `community_courses` AS b
ON a.`community_id` = b.`community_id`
JOIN `courses` AS c
ON b.`course_id` = c.`course_id`
JOIN `course_audience` AS d
ON c.`course_id` = d.`course_id`
JOIN `group_members` AS e
ON e.`group_id` = d.`audience_value`
LEFT JOIN `community_members` AS f
ON a.`community_id` = f.`community_id`
AND f.`proxy_id` = e.`proxy_id`
WHERE a.`community_template` = 'course'
AND a.`community_active` = '1'
AND c.`course_active` = '1'
AND d.`audience_type` = 'group_id'
AND d.`audience_active` = '1'
AND (d.`enroll_start` = 0 OR d.`enroll_start` <= UNIX_TIMESTAMP())
AND (d.`enroll_finish` = 0 OR d.`enroll_finish` > UNIX_TIMESTAMP())
AND (e.`start_date` = 0 OR e.`start_date` <= UNIX_TIMESTAMP())
AND (e.`finish_date` = 0 OR e.`finish_date` > UNIX_TIMESTAMP())
AND f.`cmember_id` IS NULL;

This can use some further refinement to handle people who have been deactivated, and such.