[civicrm-core.git] / CRM / Campaign / BAO / Query.php

<?php

/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.4                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2013                                |
 +--------------------------------------------------------------------+
 | This file is a part of CiviCRM.                                    |
 |                                                                    |
 | CiviCRM is free software; you can copy, modify, and distribute it  |
 | under the terms of the GNU Affero General Public License           |
 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception.   |
 |                                                                    |
 | CiviCRM is distributed in the hope that it will be useful, but     |
 | WITHOUT ANY WARRANTY; without even the implied warranty of         |
 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.               |
 | See the GNU Affero General Public License for more details.        |
 |                                                                    |
 | You should have received a copy of the GNU Affero General Public   |
 | License and the CiviCRM Licensing Exception along                  |
 | with this program; if not, contact CiviCRM LLC                     |
 | at info[AT]civicrm[DOT]org. If you have questions about the        |
 | GNU Affero General Public License or the licensing of CiviCRM,     |
 | see the CiviCRM license FAQ at http://civicrm.org/licensing        |
 +--------------------------------------------------------------------+
*/

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2013
 * $Id$
 *
 */
class CRM_Campaign_BAO_Query {
  //since normal activity clause clause get collides.
  CONST
    CIVICRM_ACTIVITY = 'civicrm_survey_activity',
    CIVICRM_ACTIVITY_TARGET = 'civicrm_survey_activity_target',
    CIVICRM_ACTIVITY_ASSIGNMENT = 'civicrm_survey_activity_assignment';

  /**
   * static field for all the campaign fields
   *
   * @var array
   * @static
   */
  static $_campaignFields = NULL;

  static $_applySurveyClause = FALSE;

  /**
   * Function get the fields for campaign.
   *
   * @return array self::$_campaignFields  an associative array of campaign fields
   * @static
   */
  static function &getFields() {
    if (!isset(self::$_campaignFields)) {
      self::$_campaignFields = array();
    }

    return self::$_campaignFields;
  }

  /**
   * if survey, campaign are involved, add the specific fields.
   *
   * @return void
   * @access public
   */
  static function select(&$query) {
    self::$_applySurveyClause = FALSE;
    if (is_array($query->_params)) {
      foreach ($query->_params as $values) {
        if (!is_array($values) || count($values) != 5) {
          continue;
        }

        list($name, $op, $value, $grouping, $wildcard) = $values;
        if ($name == 'campaign_survey_id') {
          self::$_applySurveyClause = TRUE;
          break;
        }
      }
    }
    // CRM-13810 Translate campaign_id to label for search builder
    if (is_array($query->_select)) {
      foreach($query->_select as $field => $queryString) {
        if (substr($field, -11) == 'campaign_id') {
          $query->_pseudoConstantsSelect[$field] = array(
            'pseudoField' => 'campaign_id',
            'idCol' => $field,
            'bao' => 'CRM_Activity_BAO_Activity',
          );
        }
      }
    }


    //get survey clause in force,
    //only when we have survey id.
    if (!self::$_applySurveyClause) {
      return;
    }

    //all below tables are require to fetch  result.

    //1. get survey activity target table in.
    $query->_select['survey_activity_target_contact_id'] = 'civicrm_activity_target.contact_id as survey_activity_target_contact_id';
    $query->_select['survey_activity_target_id'] = 'civicrm_activity_target.id as survey_activity_target_id';
    $query->_element['survey_activity_target_id'] = 1;
    $query->_element['survey_activity_target_contact_id'] = 1;
    $query->_tables[self::CIVICRM_ACTIVITY_TARGET] = 1;
    $query->_whereTables[self::CIVICRM_ACTIVITY_TARGET] = 1;

    //2. get survey activity table in.
    $query->_select['survey_activity_id'] = 'civicrm_activity.id as survey_activity_id';
    $query->_element['survey_activity_id'] = 1;
    $query->_tables[self::CIVICRM_ACTIVITY] = 1;
    $query->_whereTables[self::CIVICRM_ACTIVITY] = 1;

    //3. get the assignee table in.
    $query->_select['survey_interviewer_id'] = 'civicrm_activity_assignment.id as survey_interviewer_id';
    $query->_element['survey_interviewer_id'] = 1;
    $query->_tables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = 1;
    $query->_whereTables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = 1;

    //4. get survey table.
    $query->_select['campaign_survey_id'] = 'civicrm_survey.id as campaign_survey_id';
    $query->_element['campaign_survey_id'] = 1;
    $query->_tables['civicrm_survey'] = 1;
    $query->_whereTables['civicrm_survey'] = 1;

    //5. get campaign table.
    $query->_select['campaign_id'] = 'civicrm_campaign.id as campaign_id';
    $query->_element['campaign_id'] = 1;
    $query->_tables['civicrm_campaign'] = 1;
    $query->_whereTables['civicrm_campaign'] = 1;
  }

  static function where(&$query) {
    //get survey clause in force,
    //only when we have survey id.
    if (!self::$_applySurveyClause) {
      return;
    }

    $grouping = NULL;
    foreach (array_keys($query->_params) as $id) {
      if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
        $query->_useDistinct = TRUE;
      }

      self::whereClauseSingle($query->_params[$id], $query);
    }
  }

  static function whereClauseSingle(&$values, &$query) {
    //get survey clause in force,
    //only when we have survey id.
    if (!self::$_applySurveyClause) {
      return;
    }

    list($name, $op, $value, $grouping, $wildcard) = $values;

    switch ($name) {
      case 'campaign_survey_id':
        $query->_qill[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));

        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.source_record_id',
          $op, $value, 'Integer'
        );
        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_survey.id',
          $op, $value, 'Integer'
        );
        return;

      case 'survey_status_id':
        $activityStatus = CRM_Core_PseudoConstant::activityStatus();

        $query->_qill[$grouping][] = ts('Survey Status - %1', array(1 => $activityStatus[$value]));
        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.status_id',
          $op, $value, 'Integer'
        );
        return;

      case 'campaign_search_voter_for':
        if (in_array($value, array('release', 'interview'))) {
          $query->_where[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
        }
        return;

      case 'survey_interviewer_id':
        $surveyInterviewerName = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
        $query->_qill[$grouping][] = ts('Survey Interviewer - %1', array(1 => $surveyInterviewerName));
        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity_assignment.contact_id',
          $op, $value, 'Integer'
        );
        return;
    }
  }

  static function from($name, $mode, $side) {
    $from = NULL;
    //get survey clause in force,
    //only when we have survey id.
    if (!self::$_applySurveyClause) {
      return $from;
    }

    $activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
    $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
    $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
    $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);

    switch ($name) {
      case self::CIVICRM_ACTIVITY_TARGET:
        $from = " INNER JOIN civicrm_activity_contact civicrm_activity_target
   ON ( civicrm_activity_target.contact_id = contact_a.id AND civicrm_activity_target.record_type_id = $targetID) ";
        break;

      case self::CIVICRM_ACTIVITY:
        $surveyActivityTypes = CRM_Campaign_PseudoConstant::activityType();
        $surveyKeys          = "(" . implode(',', array_keys($surveyActivityTypes)) . ")";
        $from                = " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
                                 AND civicrm_activity.activity_type_id IN $surveyKeys ) ";
        break;

      case self::CIVICRM_ACTIVITY_ASSIGNMENT:
        $from = "
INNER JOIN  civicrm_activity_contact civicrm_activity_assignment ON ( civicrm_activity.id = civicrm_activity_assignment.activity_id AND
civicrm_activity_assignment.record_type_id = $assigneeID ) ";
        break;

      case 'civicrm_survey':
        $from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
        break;

      case 'civicrm_campaign':
        $from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
        break;
    }

    return $from;
  }

  static function defaultReturnProperties($mode,
    $includeCustomFields = TRUE
  ) {
    $properties = NULL;
    if ($mode & CRM_Contact_BAO_Query::MODE_CAMPAIGN) {
      $properties = array(
        'contact_id' => 1,
        'contact_type' => 1,
        'contact_sub_type' => 1,
        'sort_name' => 1,
        'display_name' => 1,
        'street_unit' => 1,
        'street_name' => 1,
        'street_number' => 1,
        'street_address' => 1,
        'city' => 1,
        'postal_code' => 1,
        'state_province' => 1,
        'country' => 1,
        'email' => 1,
        'phone' => 1,
        'survey_activity_target_id' => 1,
        'survey_activity_id' => 1,
        'survey_status_id' => 1,
        'campaign_survey_id' => 1,
        'campaign_id' => 1,
        'survey_interviewer_id' => 1,
        'survey_activity_target_contact_id' => 1,
      );
    }

    return $properties;
  }

  static function tableNames(&$tables) {}
  static function searchAction(&$row, $id) {}

  static function info(&$tables) {
    //get survey clause in force,
    //only when we have survey id.
    if (!self::$_applySurveyClause) {
      return;
    }

    $weight = end($tables);
    $tables[self::CIVICRM_ACTIVITY_TARGET] = ++$weight;
    $tables[self::CIVICRM_ACTIVITY] = ++$weight;
    $tables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = ++$weight;
    $tables['civicrm_survey'] = ++$weight;
    $tables['civicrm_campaign'] = ++$weight;
  }

  /**
   * add all the elements shared between,
   * normal voter search and voter listing (GOTV form)
   *
   * @access public
   *
   * @param CRM_Core_Form $form
   * @return void
   * @static
   */
  static function buildSearchForm(&$form) {

    $attributes = CRM_Core_DAO::getAttribute('CRM_Core_DAO_Address');
    $className = CRM_Utils_System::getClassName($form);

    $form->add('text', 'sort_name', ts('Contact Name'),
      CRM_Core_DAO::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
    );
    $form->add('text', 'street_name', ts('Street Name'), $attributes['street_name']);
    $form->add('text', 'street_number', ts('Street Number'), $attributes['street_number']);
    $form->add('text', 'street_unit', ts('Street Unit'), $attributes['street_unit']);
    $form->add('text', 'street_address', ts('Street Address'), $attributes['street_address']);
    $form->add('text', 'city', ts('City'), $attributes['city']);
    $form->add('text', 'postal_code', ts('Zip / Postal Code'), $attributes['postal_code']);

    $contactTypes = CRM_Contact_BAO_ContactType::getSelectElements();
    $form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
      array('id' => 'contact_type', 'multiple' => 'multiple', 'class' => 'crm-select2')
    );
    $groups = CRM_Core_PseudoConstant::group();
    $form->add('select', 'group', ts('Groups'), $groups, FALSE,
      array('id' => 'group', 'multiple' => 'multiple', 'class' => 'crm-select2')
    );

    $showInterviewer = FALSE;
    if (CRM_Core_Permission::check('administer CiviCampaign')) {
      $showInterviewer = TRUE;
    }
    $form->assign('showInterviewer', $showInterviewer);

    if ($showInterviewer ||
      $className == 'CRM_Campaign_Form_Gotv'
    ) {

      $form->addEntityRef('survey_interviewer_id', ts('Interviewer'), array('class' => 'big'));

      $userId = NULL;
      if (isset($form->_interviewerId) && $form->_interviewerId) {
        $userId = $form->_interviewerId;
      }
      if (!$userId) {
        $session = CRM_Core_Session::singleton();
        $userId = $session->get('userID');
      }
      if ($userId) {
        $defaults = array();
        $defaults['survey_interviewer_id'] = $userId;
        $form->setDefaults($defaults);
      }
    }

    //build ward and precinct custom fields.
    $query = '
    SELECT  fld.id, fld.label
      FROM  civicrm_custom_field fld
INNER JOIN  civicrm_custom_group grp on fld.custom_group_id = grp.id
     WHERE  grp.name = %1';
    $dao = CRM_Core_DAO::executeQuery($query, array(1 => array('Voter_Info', 'String')));
    $customSearchFields = array();
    while ($dao->fetch()) {
      foreach (array(
        'ward', 'precinct') as $name) {
        if (stripos($name, $dao->label) !== FALSE) {
          $fieldId = $dao->id;
          $fieldName = 'custom_' . $dao->id;
          $customSearchFields[$name] = $fieldName;
          CRM_Core_BAO_CustomField::addQuickFormElement($form, $fieldName, $fieldId, FALSE, FALSE);
          break;
        }
      }
    }
    $form->assign('customSearchFields', $customSearchFields);

    $surveys = CRM_Campaign_BAO_Survey::getSurveys();

    if (empty($surveys) &&
      ($className == 'CRM_Campaign_Form_Search')
    ) {
      CRM_Core_Error::statusBounce(ts('Could not find survey for %1 respondents.',
          array(1 => $form->get('op'))
        ),
        CRM_Utils_System::url('civicrm/survey/add',
          'reset=1&action=add'
        )
      );
    }

    //CRM-7406 --
    //If survey had associated campaign and
    //campaign has some contact groups, don't
    //allow to search the contacts those are not
    //in given campaign groups ( ie not in constituents )
    $props = array('class' => 'crm-select2');
    if ($form->get('searchVoterFor') == 'reserve') {
      $props['onChange'] = "buildCampaignGroups( );return false;";
    }
    $form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
  }

  /*
   * Retrieve all valid voter ids,
   * and build respective clause to restrict search.
   *
   * @param  array  $criteria an array
   * @return $voterClause as a string
   * @static
   */
  static public function voterClause($params) {
    $voterClause = array();
    $fromClause = $whereClause = NULL;
    if (!is_array($params) || empty($params)) {
      return $voterClause;
    }
    $surveyId       = CRM_Utils_Array::value('campaign_survey_id', $params);
    $searchVoterFor = CRM_Utils_Array::value('campaign_search_voter_for', $params);

    //get the survey activities.
    $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
    $status = array('Scheduled');
    if ($searchVoterFor == 'reserve') {
      $status[] = 'Completed';
    }

    $completedStatusId = NULL;
    foreach ($status as $name) {
      if ($statusId = array_search($name, $activityStatus)) {
        $statusIds[] = $statusId;
        if ($name == 'Completed') {
          $completedStatusId = $statusId;
        }
      }
    }

    $voterActValues = CRM_Campaign_BAO_Survey::getSurveyVoterInfo($surveyId, NULL, $statusIds);

    if (!empty($voterActValues)) {
      $operator = 'IN';
      $voterIds = array_keys($voterActValues);
      if ($searchVoterFor == 'reserve') {
        $operator = 'NOT IN';
        //filter out recontact survey contacts.
        $recontactInterval = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey',
          $surveyId, 'recontact_interval'
        );
        $recontactInterval = unserialize($recontactInterval);
        if ($surveyId &&
          is_array($recontactInterval) &&
          !empty($recontactInterval)
        ) {
          $voterIds = array();
          foreach ($voterActValues as $values) {
            $numOfDays = CRM_Utils_Array::value($values['result'], $recontactInterval);
            if ($numOfDays &&
              $values['status_id'] == $completedStatusId
            ) {
              $recontactIntSeconds = $numOfDays * 24 * 3600;
              $actDateTimeSeconds  = CRM_Utils_Date::unixTime($values['activity_date_time']);
              $totalSeconds        = $recontactIntSeconds + $actDateTimeSeconds;
              //don't consider completed survey activity
              //unless it fulfill recontact interval criteria.
              if ($totalSeconds <= time()) {
                continue;
              }
            }
            $voterIds[$values['voter_id']] = $values['voter_id'];
          }
        }
      }

      //lets dump these ids in tmp table and
      //use appropriate join depend on operator.
      if (!empty($voterIds)) {
        $voterIdCount = count($voterIds);

        //create temporary table to store voter ids.
        $tempTableName = CRM_Core_DAO::createTempTableName('civicrm_survey_respondent');
        CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS {$tempTableName}");

        $query = "
     CREATE TEMPORARY TABLE {$tempTableName} (
            id int unsigned NOT NULL AUTO_INCREMENT,
            survey_contact_id int unsigned NOT NULL,
  PRIMARY KEY ( id )
);
";
        CRM_Core_DAO::executeQuery($query);

        $batch = 100;
        $insertedCount = 0;
        do {
          $processIds = $voterIds;
          $insertIds = array_splice($processIds, $insertedCount, $batch);
          if (!empty($insertIds)) {
            $insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )
                     VALUES (" . implode('),(', $insertIds) . ');';
            CRM_Core_DAO::executeQuery($insertSQL);
          }
          $insertedCount += $batch;
        } while ($insertedCount < $voterIdCount);

        if ($operator == 'IN') {
          $fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
        }
        else {
          $fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
          $whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
        }
      }
    }
    $voterClause = array(
      'fromClause' => $fromClause,
      'whereClause' => $whereClause,
    );

    return $voterClause;
  }

  /**
   * Build the campaign clause for component serach.
   *
   **/
  public static function componentSearchClause(&$params, &$query) {
    $op        = CRM_Utils_Array::value('op', $params, '=');
    $campaign  = CRM_Utils_Array::value('campaign', $params);
    $tableName = CRM_Utils_Array::value('tableName', $params);
    $grouping  = CRM_Utils_Array::value('grouping', $params);
    if (CRM_Utils_System::isNull($campaign) || empty($tableName)) {
      return;
    }

    // fixme - what is the purpose of this code? $campaign should be
    // an integer, not an array
    if (is_array($campaign)) {
      foreach (array(
        'current_campaign', 'past_campaign') as $ignore) {
        $index = array_search($ignore, $campaign);
        if ($index !== FALSE)unset($campaign[$index]);
      }
    }

    $allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);

    $campaignIds = $campaignTitles = array();
    if (is_array($campaign)) {
      foreach ($campaign as $campId) {
        $campaignIds[$campId] = $campId;
        $campaignTitles[$campId] = $allCampaigns[$campId];
      }
      if (count($campaignIds) > 1) {
        $op = 'IN';
        $campaignIds = '(' . implode(',', $campaignIds) . ')';
      }
      else {
        $campaignIds = reset($campaignIds);
      }
    }
    else {
      $campaignIds = $campaign;
      if (array_key_exists($campaignIds, $allCampaigns)) {
        $campaignTitles[$campaignIds] = $allCampaigns[$campaignIds];
      }
    }
    $query->_qill[$grouping][] = ts('Campaigns %1',
      array(1 => $op)
    ) . ' ' . implode(' ' . ts('or') . ' ', $campaignTitles);
    $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.campaign_id",
      $op,
      $campaignIds,
      'Integer'
    );
    $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
  }
}
Commit	Line	Data
6a488035 TO	1	<?php
	2
	3	/*
	4	+--------------------------------------------------------------------+
232624b1	5	\| CiviCRM version 4.4 \|
6a488035 TO	6	+--------------------------------------------------------------------+
	7	\| Copyright CiviCRM LLC (c) 2004-2013 \|
	8	+--------------------------------------------------------------------+
	9	\| This file is a part of CiviCRM. \|
	10	\| \|
	11	\| CiviCRM is free software; you can copy, modify, and distribute it \|
	12	\| under the terms of the GNU Affero General Public License \|
	13	\| Version 3, 19 November 2007 and the CiviCRM Licensing Exception. \|
	14	\| \|
	15	\| CiviCRM is distributed in the hope that it will be useful, but \|
	16	\| WITHOUT ANY WARRANTY; without even the implied warranty of \|
	17	\| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. \|
	18	\| See the GNU Affero General Public License for more details. \|
	19	\| \|
	20	\| You should have received a copy of the GNU Affero General Public \|
	21	\| License and the CiviCRM Licensing Exception along \|
	22	\| with this program; if not, contact CiviCRM LLC \|
	23	\| at info[AT]civicrm[DOT]org. If you have questions about the \|
	24	\| GNU Affero General Public License or the licensing of CiviCRM, \|
	25	\| see the CiviCRM license FAQ at http://civicrm.org/licensing \|
	26	+--------------------------------------------------------------------+
	27	*/
	28
	29	/**
	30	*
	31	* @package CRM
	32	* @copyright CiviCRM LLC (c) 2004-2013
	33	* $Id$
	34	*
	35	*/
	36	class CRM_Campaign_BAO_Query {
	37	//since normal activity clause clause get collides.
	38	CONST
	39	CIVICRM_ACTIVITY = 'civicrm_survey_activity',
	40	CIVICRM_ACTIVITY_TARGET = 'civicrm_survey_activity_target',
	41	CIVICRM_ACTIVITY_ASSIGNMENT = 'civicrm_survey_activity_assignment';
	42
	43	/**
	44	* static field for all the campaign fields
	45	*
	46	* @var array
	47	* @static
	48	*/
	49	static $_campaignFields = NULL;
	50
	51	static $_applySurveyClause = FALSE;
	52
	53	/**
	54	* Function get the fields for campaign.
	55	*
	56	* @return array self::$_campaignFields an associative array of campaign fields
	57	* @static
	58	*/
	59	static function &getFields() {
	60	if (!isset(self::$_campaignFields)) {
	61	self::$_campaignFields = array();
	62	}
	63
	64	return self::$_campaignFields;
	65	}
	66
	67	/**
	68	* if survey, campaign are involved, add the specific fields.
	69	*
70	* @return void
71	* @access public
72	*/
73	static function select(&$query) {
74	self::$_applySurveyClause = FALSE;
75	if (is_array($query->_params)) {
76	foreach ($query->_params as $values) {
77	if (!is_array($values) \|\| count($values) != 5) {
78	continue;
79	}
80
81	list($name, $op, $value, $grouping, $wildcard) = $values;
82	if ($name == 'campaign_survey_id') {
83	self::$_applySurveyClause = TRUE;
84	break;
85	}
86	}
87	}
82a43d71 CW	88	// CRM-13810 Translate campaign_id to label for search builder
	89	if (is_array($query->_select)) {
	90	foreach($query->_select as $field => $queryString) {
	91	if (substr($field, -11) == 'campaign_id') {
	92	$query->_pseudoConstantsSelect[$field] = array(
	93	'pseudoField' => 'campaign_id',
	94	'idCol' => $field,
	95	'bao' => 'CRM_Activity_BAO_Activity',
	96	);
	97	}
	98	}
	99	}
	100
6a488035 TO	101
	102	//get survey clause in force,
	103	//only when we have survey id.
	104	if (!self::$_applySurveyClause) {
	105	return;
	106	}
	107
	108	//all below tables are require to fetch result.
	109
	110	//1. get survey activity target table in.
e7a308ff	111	$query->_select['survey_activity_target_contact_id'] = 'civicrm_activity_target.contact_id as survey_activity_target_contact_id';
6a488035 TO	112	$query->_select['survey_activity_target_id'] = 'civicrm_activity_target.id as survey_activity_target_id';
	113	$query->_element['survey_activity_target_id'] = 1;
	114	$query->_element['survey_activity_target_contact_id'] = 1;
	115	$query->_tables[self::CIVICRM_ACTIVITY_TARGET] = 1;
	116	$query->_whereTables[self::CIVICRM_ACTIVITY_TARGET] = 1;
	117
	118	//2. get survey activity table in.
	119	$query->_select['survey_activity_id'] = 'civicrm_activity.id as survey_activity_id';
	120	$query->_element['survey_activity_id'] = 1;
	121	$query->_tables[self::CIVICRM_ACTIVITY] = 1;
	122	$query->_whereTables[self::CIVICRM_ACTIVITY] = 1;
	123
	124	//3. get the assignee table in.
	125	$query->_select['survey_interviewer_id'] = 'civicrm_activity_assignment.id as survey_interviewer_id';
	126	$query->_element['survey_interviewer_id'] = 1;
	127	$query->_tables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = 1;
	128	$query->_whereTables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = 1;
	129
	130	//4. get survey table.
	131	$query->_select['campaign_survey_id'] = 'civicrm_survey.id as campaign_survey_id';
	132	$query->_element['campaign_survey_id'] = 1;
	133	$query->_tables['civicrm_survey'] = 1;
	134	$query->_whereTables['civicrm_survey'] = 1;
	135
	136	//5. get campaign table.
	137	$query->_select['campaign_id'] = 'civicrm_campaign.id as campaign_id';
	138	$query->_element['campaign_id'] = 1;
	139	$query->_tables['civicrm_campaign'] = 1;
	140	$query->_whereTables['civicrm_campaign'] = 1;
	141	}
	142
	143	static function where(&$query) {
	144	//get survey clause in force,
	145	//only when we have survey id.
	146	if (!self::$_applySurveyClause) {
	147	return;
	148	}
	149
	150	$grouping = NULL;
	151	foreach (array_keys($query->_params) as $id) {
395d8dc6	152	if ($query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
6a488035 TO	153	$query->_useDistinct = TRUE;
	154	}
	155
	156	self::whereClauseSingle($query->_params[$id], $query);
	157	}
	158	}
	159
	160	static function whereClauseSingle(&$values, &$query) {
	161	//get survey clause in force,
	162	//only when we have survey id.
	163	if (!self::$_applySurveyClause) {
	164	return;
	165	}
	166
	167	list($name, $op, $value, $grouping, $wildcard) = $values;
	168
	169	switch ($name) {
	170	case 'campaign_survey_id':
	171	$query->_qill[$grouping][] = ts('Survey - %1', array(1 => CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey', $value, 'title')));
	172
	173	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.source_record_id',
e7a308ff	174	$op, $value, 'Integer'
6a488035 TO	175	);
6a488035 TO	176	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_survey.id',
e7a308ff	177	$op, $value, 'Integer'
6a488035 TO	178	);
	179	return;
	180
	181	case 'survey_status_id':
	182	$activityStatus = CRM_Core_PseudoConstant::activityStatus();
	183
	184	$query->_qill[$grouping][] = ts('Survey Status - %1', array(1 => $activityStatus[$value]));
	185	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity.status_id',
e7a308ff	186	$op, $value, 'Integer'
6a488035 TO	187	);
	188	return;
	189
	190	case 'campaign_search_voter_for':
91da6cd5	191	if (in_array($value, array('release', 'interview'))) {
6a488035 TO	192	$query->_where[$grouping][] = '(civicrm_activity.is_deleted = 0 OR civicrm_activity.is_deleted IS NULL)';
	193	}
	194	return;
	195
	196	case 'survey_interviewer_id':
56ecdf7b	197	$surveyInterviewerName = CRM_Core_DAO::getFieldValue('CRM_Contact_DAO_Contact', $value, 'sort_name');
6a488035	198	$query->_qill[$grouping][] = ts('Survey Interviewer - %1', array(1 => $surveyInterviewerName));
e7a308ff	199	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity_assignment.contact_id',
e7a308ff	200	$op, $value, 'Integer'
6a488035 TO	201	);
	202	return;
	203	}
	204	}
	205
	206	static function from($name, $mode, $side) {
	207	$from = NULL;
	208	//get survey clause in force,
	209	//only when we have survey id.
	210	if (!self::$_applySurveyClause) {
	211	return $from;
	212	}
	213
e7e657f0	214	$activityContacts = CRM_Core_OptionGroup::values('activity_contacts', FALSE, FALSE, FALSE, NULL, 'name');
9e74e3ce	215	$sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts);
	216	$assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts);
	217	$targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts);
8ef12e64	218
6a488035 TO	219	switch ($name) {
6a488035 TO	220	case self::CIVICRM_ACTIVITY_TARGET:
e7a308ff	221	$from = " INNER JOIN civicrm_activity_contact civicrm_activity_target
9e74e3ce	222	ON ( civicrm_activity_target.contact_id = contact_a.id AND civicrm_activity_target.record_type_id = $targetID) ";
6a488035 TO	223	break;
	224
	225	case self::CIVICRM_ACTIVITY:
	226	$surveyActivityTypes = CRM_Campaign_PseudoConstant::activityType();
	227	$surveyKeys = "(" . implode(',', array_keys($surveyActivityTypes)) . ")";
5b5da1ef	228	$from = " INNER JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
6a488035 TO	229	AND civicrm_activity.activity_type_id IN $surveyKeys ) ";
	230	break;
	231
	232	case self::CIVICRM_ACTIVITY_ASSIGNMENT:
	233	$from = "
e7a308ff	234	INNER JOIN civicrm_activity_contact civicrm_activity_assignment ON ( civicrm_activity.id = civicrm_activity_assignment.activity_id AND
9e74e3ce	235	civicrm_activity_assignment.record_type_id = $assigneeID ) ";
6a488035 TO	236	break;
	237
	238	case 'civicrm_survey':
	239	$from = " INNER JOIN civicrm_survey ON ( civicrm_survey.id = civicrm_activity.source_record_id ) ";
	240	break;
	241
	242	case 'civicrm_campaign':
	243	$from = " $side JOIN civicrm_campaign ON ( civicrm_campaign.id = civicrm_survey.campaign_id ) ";
	244	break;
	245	}
	246
	247	return $from;
	248	}
	249
	250	static function defaultReturnProperties($mode,
	251	$includeCustomFields = TRUE
	252	) {
	253	$properties = NULL;
	254	if ($mode & CRM_Contact_BAO_Query::MODE_CAMPAIGN) {
	255	$properties = array(
	256	'contact_id' => 1,
	257	'contact_type' => 1,
	258	'contact_sub_type' => 1,
	259	'sort_name' => 1,
	260	'display_name' => 1,
	261	'street_unit' => 1,
	262	'street_name' => 1,
	263	'street_number' => 1,
	264	'street_address' => 1,
	265	'city' => 1,
	266	'postal_code' => 1,
	267	'state_province' => 1,
	268	'country' => 1,
	269	'email' => 1,
	270	'phone' => 1,
	271	'survey_activity_target_id' => 1,
	272	'survey_activity_id' => 1,
	273	'survey_status_id' => 1,
	274	'campaign_survey_id' => 1,
	275	'campaign_id' => 1,
	276	'survey_interviewer_id' => 1,
	277	'survey_activity_target_contact_id' => 1,
	278	);
	279	}
	280
	281	return $properties;
	282	}
	283
	284	static function tableNames(&$tables) {}
	285	static function searchAction(&$row, $id) {}
	286
	287	static function info(&$tables) {
	288	//get survey clause in force,
	289	//only when we have survey id.
	290	if (!self::$_applySurveyClause) {
	291	return;
	292	}
	293
	294	$weight = end($tables);
	295	$tables[self::CIVICRM_ACTIVITY_TARGET] = ++$weight;
	296	$tables[self::CIVICRM_ACTIVITY] = ++$weight;
	297	$tables[self::CIVICRM_ACTIVITY_ASSIGNMENT] = ++$weight;
	298	$tables['civicrm_survey'] = ++$weight;
	299	$tables['civicrm_campaign'] = ++$weight;
300	}
301
302	/**
303	* add all the elements shared between,
304	* normal voter search and voter listing (GOTV form)
305	*
306	* @access public
307	*
56ecdf7b	308	* @param CRM_Core_Form $form
6a488035 TO	309	* @return void
	310	* @static
	311	*/
	312	static function buildSearchForm(&$form) {
	313
	314	$attributes = CRM_Core_DAO::getAttribute('CRM_Core_DAO_Address');
	315	$className = CRM_Utils_System::getClassName($form);
	316
	317	$form->add('text', 'sort_name', ts('Contact Name'),
	318	CRM_Core_DAO::getAttribute('CRM_Contact_DAO_Contact', 'sort_name')
	319	);
	320	$form->add('text', 'street_name', ts('Street Name'), $attributes['street_name']);
	321	$form->add('text', 'street_number', ts('Street Number'), $attributes['street_number']);
	322	$form->add('text', 'street_unit', ts('Street Unit'), $attributes['street_unit']);
	323	$form->add('text', 'street_address', ts('Street Address'), $attributes['street_address']);
	324	$form->add('text', 'city', ts('City'), $attributes['city']);
	325	$form->add('text', 'postal_code', ts('Zip / Postal Code'), $attributes['postal_code']);
	326
	327	$contactTypes = CRM_Contact_BAO_ContactType::getSelectElements();
	328	$form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
ab345ca5	329	array('id' => 'contact_type', 'multiple' => 'multiple', 'class' => 'crm-select2')
6a488035 TO	330	);
	331	$groups = CRM_Core_PseudoConstant::group();
	332	$form->add('select', 'group', ts('Groups'), $groups, FALSE,
ab345ca5	333	array('id' => 'group', 'multiple' => 'multiple', 'class' => 'crm-select2')
6a488035 TO	334	);
	335
	336	$showInterviewer = FALSE;
	337	if (CRM_Core_Permission::check('administer CiviCampaign')) {
	338	$showInterviewer = TRUE;
	339	}
	340	$form->assign('showInterviewer', $showInterviewer);
	341
	342	if ($showInterviewer \|\|
	343	$className == 'CRM_Campaign_Form_Gotv'
	344	) {
6a488035	345
56ecdf7b	346	$form->addEntityRef('survey_interviewer_id', ts('Interviewer'), array('class' => 'big'));
6a488035 TO	347
	348	$userId = NULL;
	349	if (isset($form->_interviewerId) && $form->_interviewerId) {
	350	$userId = $form->_interviewerId;
	351	}
	352	if (!$userId) {
395d8dc6	353	$session = CRM_Core_Session::singleton();
6a488035 TO	354	$userId = $session->get('userID');
	355	}
	356	if ($userId) {
	357	$defaults = array();
	358	$defaults['survey_interviewer_id'] = $userId;
6a488035 TO	359	$form->setDefaults($defaults);
	360	}
	361	}
	362
	363	//build ward and precinct custom fields.
	364	$query = '
	365	SELECT fld.id, fld.label
	366	FROM civicrm_custom_field fld
	367	INNER JOIN civicrm_custom_group grp on fld.custom_group_id = grp.id
	368	WHERE grp.name = %1';
	369	$dao = CRM_Core_DAO::executeQuery($query, array(1 => array('Voter_Info', 'String')));
	370	$customSearchFields = array();
	371	while ($dao->fetch()) {
	372	foreach (array(
	373	'ward', 'precinct') as $name) {
	374	if (stripos($name, $dao->label) !== FALSE) {
	375	$fieldId = $dao->id;
	376	$fieldName = 'custom_' . $dao->id;
	377	$customSearchFields[$name] = $fieldName;
	378	CRM_Core_BAO_CustomField::addQuickFormElement($form, $fieldName, $fieldId, FALSE, FALSE);
	379	break;
	380	}
	381	}
	382	}
	383	$form->assign('customSearchFields', $customSearchFields);
	384
	385	$surveys = CRM_Campaign_BAO_Survey::getSurveys();
	386
	387	if (empty($surveys) &&
	388	($className == 'CRM_Campaign_Form_Search')
	389	) {
	390	CRM_Core_Error::statusBounce(ts('Could not find survey for %1 respondents.',
	391	array(1 => $form->get('op'))
	392	),
	393	CRM_Utils_System::url('civicrm/survey/add',
	394	'reset=1&action=add'
	395	)
	396	);
	397	}
	398
	399	//CRM-7406 --
	400	//If survey had associated campaign and
	401	//campaign has some contact groups, don't
	402	//allow to search the contacts those are not
	403	//in given campaign groups ( ie not in constituents )
4145f041	404	$props = array('class' => 'crm-select2');
6a488035	405	if ($form->get('searchVoterFor') == 'reserve') {
4145f041	406	$props['onChange'] = "buildCampaignGroups( );return false;";
6a488035	407	}
4145f041	408	$form->add('select', 'campaign_survey_id', ts('Survey'), $surveys, TRUE, $props);
6a488035 TO	409	}
	410
	411	/*
	412	* Retrieve all valid voter ids,
	413	* and build respective clause to restrict search.
	414	*
	415	* @param array $criteria an array
	416	* @return $voterClause as a string
	417	* @static
	418	*/
a019ffee	419	static public function voterClause($params) {
6a488035 TO	420	$voterClause = array();
	421	$fromClause = $whereClause = NULL;
	422	if (!is_array($params) \|\| empty($params)) {
	423	return $voterClause;
	424	}
	425	$surveyId = CRM_Utils_Array::value('campaign_survey_id', $params);
6a488035 TO	426	$searchVoterFor = CRM_Utils_Array::value('campaign_search_voter_for', $params);
	427
	428	//get the survey activities.
	429	$activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
	430	$status = array('Scheduled');
	431	if ($searchVoterFor == 'reserve') {
	432	$status[] = 'Completed';
	433	}
	434
	435	$completedStatusId = NULL;
	436	foreach ($status as $name) {
	437	if ($statusId = array_search($name, $activityStatus)) {
	438	$statusIds[] = $statusId;
	439	if ($name == 'Completed') {
	440	$completedStatusId = $statusId;
	441	}
	442	}
	443	}
	444
	445	$voterActValues = CRM_Campaign_BAO_Survey::getSurveyVoterInfo($surveyId, NULL, $statusIds);
	446
	447	if (!empty($voterActValues)) {
	448	$operator = 'IN';
	449	$voterIds = array_keys($voterActValues);
	450	if ($searchVoterFor == 'reserve') {
	451	$operator = 'NOT IN';
	452	//filter out recontact survey contacts.
	453	$recontactInterval = CRM_Core_DAO::getFieldValue('CRM_Campaign_DAO_Survey',
	454	$surveyId, 'recontact_interval'
	455	);
	456	$recontactInterval = unserialize($recontactInterval);
	457	if ($surveyId &&
	458	is_array($recontactInterval) &&
	459	!empty($recontactInterval)
	460	) {
	461	$voterIds = array();
	462	foreach ($voterActValues as $values) {
	463	$numOfDays = CRM_Utils_Array::value($values['result'], $recontactInterval);
	464	if ($numOfDays &&
	465	$values['status_id'] == $completedStatusId
	466	) {
	467	$recontactIntSeconds = $numOfDays * 24 * 3600;
	468	$actDateTimeSeconds = CRM_Utils_Date::unixTime($values['activity_date_time']);
	469	$totalSeconds = $recontactIntSeconds + $actDateTimeSeconds;
	470	//don't consider completed survey activity
	471	//unless it fulfill recontact interval criteria.
	472	if ($totalSeconds <= time()) {
	473	continue;
	474	}
	475	}
	476	$voterIds[$values['voter_id']] = $values['voter_id'];
	477	}
	478	}
	479	}
	480
	481	//lets dump these ids in tmp table and
	482	//use appropriate join depend on operator.
	483	if (!empty($voterIds)) {
	484	$voterIdCount = count($voterIds);
	485
	486	//create temporary table to store voter ids.
	487	$tempTableName = CRM_Core_DAO::createTempTableName('civicrm_survey_respondent');
	488	CRM_Core_DAO::executeQuery("DROP TABLE IF EXISTS {$tempTableName}");
	489
490	$query = "
491	CREATE TEMPORARY TABLE {$tempTableName} (
492	id int unsigned NOT NULL AUTO_INCREMENT,
493	survey_contact_id int unsigned NOT NULL,
494	PRIMARY KEY ( id )
495	);
496	";
497	CRM_Core_DAO::executeQuery($query);
498
499	$batch = 100;
500	$insertedCount = 0;
501	do {
502	$processIds = $voterIds;
503	$insertIds = array_splice($processIds, $insertedCount, $batch);
504	if (!empty($insertIds)) {
505	$insertSQL = "INSERT IGNORE INTO {$tempTableName}( survey_contact_id )
506	VALUES (" . implode('),(', $insertIds) . ');';
507	CRM_Core_DAO::executeQuery($insertSQL);
508	}
509	$insertedCount += $batch;
510	} while ($insertedCount < $voterIdCount);
511
512	if ($operator == 'IN') {
513	$fromClause = " INNER JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
514	}
515	else {
516	$fromClause = " LEFT JOIN {$tempTableName} ON ( {$tempTableName}.survey_contact_id = contact_a.id )";
517	$whereClause = "( {$tempTableName}.survey_contact_id IS NULL )";
518	}
519	}
520	}
521	$voterClause = array(
522	'fromClause' => $fromClause,
523	'whereClause' => $whereClause,
524	);
525
526	return $voterClause;
527	}
528
529	/**
530	* Build the campaign clause for component serach.
531	*
532	**/
533	public static function componentSearchClause(&$params, &$query) {
534	$op = CRM_Utils_Array::value('op', $params, '=');
535	$campaign = CRM_Utils_Array::value('campaign', $params);
536	$tableName = CRM_Utils_Array::value('tableName', $params);
537	$grouping = CRM_Utils_Array::value('grouping', $params);
538	if (CRM_Utils_System::isNull($campaign) \|\| empty($tableName)) {
539	return;
540	}
541
542	// fixme - what is the purpose of this code? $campaign should be
543	// an integer, not an array
544	if (is_array($campaign)) {
545	foreach (array(
546	'current_campaign', 'past_campaign') as $ignore) {
547	$index = array_search($ignore, $campaign);
548	if ($index !== FALSE)unset($campaign[$index]);
549	}
550	}
551
552	$allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE);
553
554	$campaignIds = $campaignTitles = array();
555	if (is_array($campaign)) {
556	foreach ($campaign as $campId) {
557	$campaignIds[$campId] = $campId;
558	$campaignTitles[$campId] = $allCampaigns[$campId];
559	}
560	if (count($campaignIds) > 1) {
561	$op = 'IN';
562	$campaignIds = '(' . implode(',', $campaignIds) . ')';
563	}
564	else {
565	$campaignIds = reset($campaignIds);
566	}
567	}
568	else {
569	$campaignIds = $campaign;
570	if (array_key_exists($campaignIds, $allCampaigns)) {
571	$campaignTitles[$campaignIds] = $allCampaigns[$campaignIds];
572	}
573	}
574	$query->_qill[$grouping][] = ts('Campaigns %1',
575	array(1 => $op)
576	) . ' ' . implode(' ' . ts('or') . ' ', $campaignTitles);
577	$query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause("{$tableName}.campaign_id",
578	$op,
579	$campaignIds,
580	'Integer'
581	);
582	$query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
583	}
584	}
585