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

<?php
/*
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC. All rights reserved.                        |
 |                                                                    |
 | This work is published under the GNU AGPLv3 license with some      |
 | permitted exceptions and without any warranty. For full license    |
 | and copyright information, see https://civicrm.org/licensing       |
 +--------------------------------------------------------------------+
 */

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC https://civicrm.org/licensing
 */
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
   */
  public static $_campaignFields = NULL;

  public static $_applySurveyClause = FALSE;

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

    return self::$_campaignFields;
  }

  /**
   * If survey, campaign are involved, add the specific fields.
   *
   * @param CRM_Contact_BAO_Contact $query
   */
  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
    // CRM-14238 Only translate when we are in contact mode
    // Other modes need the untranslated data for export and other functions
    if (is_array($query->_select) && $query->_mode == CRM_Contact_BAO_Query::MODE_CONTACTS) {
      foreach ($query->_select as $field => $queryString) {
        if (substr($field, -11) == 'campaign_id') {
          $query->_pseudoConstantsSelect[$field] = [
            '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;
  }

  /**
   * @param $query
   */
  public 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);
    }
  }

  /**
   * @param $values
   * @param $query
   */
  public 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', [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', [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, ['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', [1 => $surveyInterviewerName]);
        $query->_where[$grouping][] = CRM_Contact_BAO_Query::buildClause('civicrm_activity_assignment.contact_id',
          $op, $value, 'Integer'
        );
        return;
    }
  }

  /**
   * @param string $name
   * @param $mode
   * @param $side
   *
   * @return null|string
   */
  public 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_Activity_BAO_ActivityContact::buildOptions('record_type_id', 'validate');
    $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;
  }

  /**
   * @param $mode
   * @param bool $includeCustomFields
   *
   * @return array|null
   */
  public static function defaultReturnProperties(
    $mode,
    $includeCustomFields = TRUE
  ) {
    $properties = NULL;
    if ($mode & CRM_Contact_BAO_Query::MODE_CAMPAIGN) {
      $properties = [
        '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;
  }

  /**
   * @param $tables
   */
  public static function tableNames(&$tables) {
  }

  /**
   * @param $row
   * @param int $id
   */
  public static function searchAction(&$row, $id) {
  }

  /**
   * @param $tables
   */
  public 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)
   *
   * @param CRM_Core_Form $form
   */
  public 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('Postal Code'), $attributes['postal_code']);

    //@todo FIXME - using the CRM_Core_DAO::VALUE_SEPARATOR creates invalid html - if you can find the form
    // this is loaded onto then replace with something like '__' & test
    $separator = CRM_Core_DAO::VALUE_SEPARATOR;
    $contactTypes = CRM_Contact_BAO_ContactType::getSelectElements(FALSE, TRUE, $separator);
    $form->add('select', 'contact_type', ts('Contact Type(s)'), $contactTypes, FALSE,
      ['id' => 'contact_type', 'multiple' => 'multiple', 'class' => 'crm-select2']
    );
    $groups = CRM_Core_PseudoConstant::nestedGroup();
    $form->add('select', 'group', ts('Groups'), $groups, FALSE,
      ['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'), ['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 = [];
        $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, [1 => ['Voter_Info', 'String']]);
    $customSearchFields = [];
    while ($dao->fetch()) {
      foreach (['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);
          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.',
          [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 = ['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
   */

  /**
   * @param array $params
   *
   * @return array
   */
  public static function voterClause($params) {
    $voterClause = [];
    $fromClause = $whereClause = NULL;
    if (!is_array($params) || empty($params)) {
      return $voterClause;
    }
    $surveyId = $params['campaign_survey_id'] ?? NULL;
    $searchVoterFor = $params['campaign_search_voter_for'] ?? NULL;

    //get the survey activities.
    $activityStatus = CRM_Core_PseudoConstant::activityStatus('name');
    $status = ['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 = CRM_Utils_String::unserialize($recontactInterval);
        if ($surveyId &&
          is_array($recontactInterval) &&
          !empty($recontactInterval)
        ) {
          $voterIds = [];
          foreach ($voterActValues as $values) {
            $numOfDays = $recontactInterval[$values['result']] ?? NULL;
            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.
        $tempTable = CRM_Utils_SQL_TempTable::build();
        $tempTableName = $tempTable->getName();
        CRM_Core_DAO::executeQuery("DROP TEMPORARY TABLE IF EXISTS {$tempTableName}");
        $tempTable->createWithColumns('id int unsigned NOT NULL AUTO_INCREMENT, survey_contact_id int unsigned NOT NULL, PRIMARY KEY ( id )');

        $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 = [
      'fromClause' => $fromClause,
      'whereClause' => $whereClause,
    ];

    return $voterClause;
  }

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