I am using Drupal 7 and CiviCRM 4.6.28.
I am trying to create a report that will display all of the users who registered for an event in 2015 and 2016, but not yet in 2017. I have the report working to the point of displaying all who registered in 2015, 16, and 17. I am not sure how to go about removing those who have not registered in 2017. The idea I've been trying is to create an array of contact_ids that are registered in 2017, and then use that array in the WHERE clause, to only select rows that do not contain that contact_id. It is still displaying users who have registered in 2017, though.
I am running this query in the where() function in the report php file.
Here are the contents of the where() function:
$sql = "SELECT {$this->_aliases['civicrm_participant']}.contact_id,
{$this->_aliases['civicrm_participant']}.register_date
FROM civicrm_participant {$this->_aliases['civicrm_participant']}";
$dao = CRM_Core_DAO::executeQuery($sql);
$i = 0;
$registered = array();
while($dao->fetch()) {
if (strpos($dao->civicrm_participant_register_date, '2017') !== false )
{
$registered[$i] = $dao->civicrm_participant_contact_id;
$i++;
}
}
$this->_where = "WHERE ({$this->_aliases['civicrm_event']}.title = 'SF Open
Studios 2017'
OR {$this->_aliases['civicrm_event']}.title = 'SF Open Studios 2016'
OR {$this->_aliases['civicrm_event']}.title = 'SF Open Studios 2015')
AND {$this->_aliases['civicrm_participant']}.contact_id
NOT IN ( '" . implode($registered, "', '") . "' )";
}