science-ation/admin/communication.inc.php

<?
	$mailqueries=array(
		"myself"=>array("name"=>"Yourself (for testing)","query"=>"SELECT users.id FROM users WHERE users.id='{$_SESSION['users_id']}'"),

		"committee_all"=>array("name"=>"Committee members (all)","query"=>
			"SELECT users.id FROM users
			JOIN accounts ON users.accounts_id=accounts.id
			JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type = 'committee' AND accounts.deleted = 'no' "),

		/* The WHERE clause evaluates which rows to add to the GROUP
		BY, the HAVING clase evaluates which grouped rows show up.  We
		want to to evaluate 'deleted' AFTER the grouping, so we catch
		the case where the MAX(conferences_id) has deleted='yes'.  If we use WHERE
		deleted='no', we'll only add non-deleted rows to the group, and
		end up picking up a user active in, say 2007 and 2008, but
		deleted in 2009. */
		"judges_all"=>array("name"=>"Judges from all conferences (except deleted judges)","query"=>
			"SELECT firstname, lastname, email, deleted, MAX(conferences_id) FROM users 
			JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type ='judge' GROUP BY users.accounts_id HAVING deleted='no' ORDER BY email"),

		"judges_active_thisconference"=>array("name"=>"Judges active for this conference", "query"=>
			"SELECT users.id FROM users
			LEFT JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type='judge' AND conferences_id={$conference['id']} AND deleted='no' AND user_roles.active='yes' ORDER BY email"),

		"judges_inactive_thisconference"=>array("name"=>"Judges in the conference that are not active", "query"=>
			"SELECT users.id FROM users
			LEFT JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type='judge' AND conferences_id={$conference['id']} AND deleted='no' AND user_roles.active='no' ORDER BY email"),

		"judges_inactive_allconferences"=>array("name"=>"Judges inactive for any conference", "query"=>
			"SELECT users.id FROM users
			LEFT JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type='judge' AND deleted='no' AND user_roles.active='no' ORDER BY email"),

		"judges_active_complete_thisconference"=>array("name"=>"Judges active for this conference and complete", "query"=>
			"SELECT users.id FROM users
			LEFT JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.id
			WHERE roles.type = 'judge' AND conferences_id = {$conference['id']}
			AND user_roles.complete='yes'
			AND deleted='no'
			AND user_roles.active='yes'
			ORDER BY email"),

		"judges_active_incomplete_thisconference"=>array("name"=>"Judges active for this conference but not complete", "query"=>
			"SELECT users.id FROM users
			LEFT JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.id
			WHERE roles.type = 'judge' AND conferences_id = {$conference['id']}
			AND user_roles.complete='no'
			AND deleted='no'
			AND user_roles.active='yes'
			ORDER BY email"),

		"participants_all_thisconference"=>array("name"=>"Participants (all) for this conference","query"=>
			"SELECT users.id
			 FROM users
			 JOIN user_roles ON user_roles.users_id=users.id
			 JOIN roles ON user_roles.roles_id=roles.id
			 WHERE users.conferences_id='{$conference['id']}'
			 AND roles.type='participant'"),

		"participants_complete_thisconference"=>array("name"=>"Participants complete/paymentpending for this conference","query"=>
			"SELECT users.id FROM users
			JOIN registrations ON users.registrations_id=registrations.id
			JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type = 'participant'
			AND registrations.conferences_id='".$conference['id']."' 
			AND ( registrations.status='complete' OR registrations.status='paymentpending')"),

		"participants_complete_paymentpending_thisconference"=>array("name"=>"Participants payment pending for this conference","query"=>
			"SELECT users.id FROM users
			JOIN registrations ON users.registrations_id=registrations.id
			JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type = 'participant'
			AND registrations.conferences_id='".$conference['id']."' 
			AND registrations.status='paymentpending'"),

		"participants_notcomplete_thisconference"=>array("name"=>"Participants not complete for this conference","query"=>
			"SELECT users.id
			 FROM users
			 JOIN user_roles ON user_roles.users_id=users.id
			 JOIN roles ON user_roles.roles_id=roles.id
			 LEFT JOIN registrations ON users.registrations_id=registrations.id
			 WHERE users.conferences_id='{$conference['id']}'
			 AND roles.type='participant'
			 AND (registrations.status IS NULL OR registrations.status='open' OR registrations.status='new')"),


/*  // FIXME - not sure if this has a sensible equivalent when dealing with "conferences" rather than fair years
		"participants_complete_lastyear"=>array("name"=>"Participants complete last year","query"=>
			"SELECT firstname, lastname, students.email FROM students,registrations WHERE students.registrations_id=registrations.id AND registrations.year='".($config['FAIRYEAR']-1)."' AND ( registrations.status='complete' OR registrations.status='paymentpending') ORDER BY students.email"),
		"participants_complete_allconferences"=>array("name"=>"Participants complete for all conferences","query"=>

			"SELECT users.id, users.firstname, users.lastname, accounts.email FROM users 
			JOIN accounts on users.accounts_id=accounts.id
			JOIN registrations ON users.registrations_id=registrations.id
			JOIN user_roles ON user_roles.users_id = users.id
			JOIN roles ON roles.id = user_roles.roles_id
			WHERE roles.type = 'participant'
			AND (registrations.status='complete' OR registrations.status='paymentpending') ORDER BY accounts.email"),

*/
		"participants_cwsf_thisconference"=>array("name"=>"CWSF Winners for this conference","query"=>"
		SELECT users.id
			FROM award_awards
			JOIN award_prizes ON award_prizes.award_awards_id=award_awards.id
			JOIN winners ON winners.awards_prizes_id=award_prizes.id
			JOIN projects ON winners.projects_id=projects.id
			JOIN registrations ON projects.registrations_id=registrations.id
			JOIN users ON users.registrations_id=registrations.id
			WHERE award_awards.cwsfaward='1' 
			AND winners.conferences_id='".$conference['id']."' "),

		"sponsors"=>array("name"=>"Organization sponsors","query"=>
			"SELECT id, organization, email FROM sponsors WHERE email!='' ORDER BY email"),

		"sponsors_primarycontacts"=>array("name"=>"Organization sponsors (primary contacts)","query"=>
			"SELECT users.id, conferences_id, organization, firstname, lastname, email, `primary` 
                FROM users
		JOIN user_roles on users.id = user_roles.id
		JOIN roles ON user_roles.roles_id = roles.id
                WHERE 
                    roles.type = 'sponsor'
                    AND email != ''
                    AND deleted='no'
		    AND `primary`='yes'
                ORDER BY email
                "),
		"sponsors_allcontacts"=>array("name"=>"Organization sponsors (all contacts)","query"=>
		"SELECT DISTINCT(users.email), sponsors.organization, users.firstname, users.lastname, users.email
		FROM sponsors
		JOIN users_sponsor ON users_sponsor.sponsors_id = sponsors.id
		JOIN users ON users.id = users_sponsor.users_id
		JOIN user_roles ON user_roles.users_id = users.id
		JOIN roles ON roles.id = user_roles.roles_id
		WHERE
		roles.type = 'sponsor'
		AND users.deleted='no'
		AND users.email!=''
		ORDER BY users.email"),

/*
		"special_award_sponsors_unconfirmed"=>array("name"=>"Special award sponsors (unconfirmed only)","query"=>
			"SELECT DISTINCT(award_sponsors.id), organization, firstname, lastname, award_contacts.email FROM award_sponsors, award_awards, award_contacts WHERE award_awards.sponsors_id=award_sponsors.id AND award_contacts.award_sponsors_id=award_sponsors.id AND award_sponsors.confirmed='no' AND award_awards.award_types_id='2' AND award_contacts.year='".$config['FAIRYEAR']."'"),

		"special_award_sponsors_all"=>array("name"=>"Special award sponsors (all)","query"=>
			"SELECT DISTINCT(award_sponsors.id), organization, firstname, lastname, award_contacts.email FROM award_sponsors, award_awards, award_contacts WHERE award_awards.sponsors_id=award_sponsors.id AND award_contacts.award_sponsors_id=award_sponsors.id AND award_awards.award_types_id='2' AND award_contacts.year='".$config['FAIRYEAR']."'"),

*/
		"school_principals"=>array("name"=>"School principals this conference","query"=>
			"SELECT principal_uid AS id FROM schools
			WHERE schools.conferences_id='".$conference['id']."'
			AND principal_uid>0
			 "),

		"school_scienceheads"=>array("name"=>"School science heads this conference","query"=>
			"SELECT sciencehead_uid AS id FROM schools
			WHERE schools.conferences_id='".$conference['id']."'
			AND sciencehead_uid>0
			"),

		"teachers_with_school"=>array("name"=>"Teachers with a school in this conference","query"=>
			"SELECT users.id
			FROM users
			JOIN schools ON users.schools_id=schools.id
			JOIN user_roles ON users.id=user_roles.users_id
			JOIN roles ON user_roles.roles_id=roles.id
			WHERE schools.conferences_id='".$conference['id']."'
			AND roles.type='teacher'
			"),

		"teachers_without_school"=>array("name"=>"Teachers without a school in this conference","query"=>
			"SELECT users.id
			FROM users
			JOIN user_roles ON users.id=user_roles.users_id
			JOIN roles ON user_roles.roles_id=roles.id
			WHERE roles.type='teacher'
			AND users.conferences_id='".$conference['id']."'
			AND (users.schools_id='' OR users.schools_id=0)
			"),

/*
		"school_teachers_thisconference"=>array("name"=>"Teachers (as entered by students) for this conference","query"=>
			"SELECT DISTINCT(teacheremail) AS email, teachername AS firstname FROM students WHERE conferences_id='".$conference['id']."' AND teacheremail!=''"),
			*/
/* // FIXME again, not sure that this has a sensible equivalent with conferences
		"school_teachers_lastyear"=>array("name"=>"Teachers (as entered by students) last year","query"=>
			"SELECT DISTINCT(teacheremail) AS email, teachername AS firstname FROM students WHERE year='".($config['FAIRYEAR']-1)."' AND teacheremail!=''"),
*/
/*
		"school_teachers_allconferences"=>array("name"=>"Teachers (as entered by students) all conferences","query"=>
			"SELECT DISTINCT(teacheremail) AS email, teachername AS firstname FROM students WHERE teacheremail!=''"),
			*/
/* Volunteers */
		"volunteers_active_complete_thisconference"=>array("name"=>"Volunteers active for this conference and complete", "query"=>
			"SELECT users.id, firstname, lastname, email
			FROM users LEFT JOIN user_roles ON user_roles.users_id = users.id JOIN roles ON roles.id = user_roles.roles_id
			WHERE users.conferences_id = '".$conference['id']."'
			AND roles.type='volunteer'
			AND user_roles.complete='yes'
			AND user_roles.active='yes'
			AND users.deleted='no'
			ORDER BY email"),

		"volunteers_active_incomplete_thisconference"=>array("name"=>"Volunteers active for this conference but not complete", "query"=>
			"SELECT users.id, firstname, lastname, email
			FROM users LEFT JOIN user_roles ON user_roles.users_id = users.id JOIN roles ON roles.id = user_roles.roles_id
			WHERE users.conferences_id = '".$conference['id']."'
			AND roles.type='volunteer'
			AND user_roles.complete='no'
			AND user_roles.active='yes'
			AND users.deleted='no'
			ORDER BY email"),

		"accounts_email_unconfirmed" => array("name" => "Users active in any active conference with unconfirmed e-mail addresses", "query" =>
			"SELECT users.id
			FROM users
			JOIN accounts ON users.accounts_id = accounts.id
			JOIN conferences ON users.conferences_id = conferences.id
			WHERE conferences.status = 'running'
			AND accounts.pendingemail IS NOT NULL
			AND accounts.pendingemail != ''
			"),

		"accounts_email_unconfirmed_thisconference" => array("name" => "Users active for this conference with unconfirmed e-mail addresses", "query" =>
			"SELECT users.id
			FROM users JOIN accounts ON users.accounts_id = accounts.id
			WHERE users.conferences_id = {$conference['id']}
			AND accounts.pendingemail IS NOT NULL
			AND accounts.pendingemail != ''
			"),
		);
?>