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

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 4.6                                                |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2014                                |
 +--------------------------------------------------------------------+
 | 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.                                       |
 |                                                                    |
 | 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 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-2014
 * $Id$
 *
 */
class CRM_Mailing_BAO_Query {

  static $_mailingFields = NULL;

  /**
   * @return array|null
   */
  public static function &getFields() {
    if (!self::$_mailingFields) {
      self::$_mailingFields = array();
      $_mailingFields['mailing_id'] = array(
        'name' => 'mailing_id',
        'title' => 'Mailing ID',
        'where' => 'civicrm_mailing.id',
      );
    }
    return self::$_mailingFields;
  }

  /**
   * If mailings are involved, add the specific Mailing fields
   *
   * @param $query
   *
   * @return void
   */
  public static function select(&$query) {
    // if Mailing mode add mailing id
    if ($query->_mode & CRM_Contact_BAO_Query::MODE_MAILING) {
      $query->_select['mailing_id'] = "civicrm_mailing.id as mailing_id";
      $query->_element['mailing_id'] = 1;

      // base table is contact, so join recipients to it
      $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients']
        = " INNER JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id ";

      $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;

      // get mailing name
      if (!empty($query->_returnProperties['mailing_name'])) {
        $query->_select['mailing_name'] = "civicrm_mailing.name as mailing_name";
        $query->_element['mailing_name'] = 1;
      }

      // get mailing subject
      if (!empty($query->_returnProperties['mailing_subject'])) {
        $query->_select['mailing_subject'] = "civicrm_mailing.subject as mailing_subject";
        $query->_element['mailing_subject'] = 1;
      }

      // get mailing status
      if (!empty($query->_returnProperties['mailing_job_status'])) {
        $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job']
          = " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
        $query->_select['mailing_job_status'] = "civicrm_mailing_job.status as mailing_job_status";
        $query->_element['mailing_job_status'] = 1;
      }

      // get email on hold
      if (!empty($query->_returnProperties['email_on_hold'])) {
        $query->_select['email_on_hold'] = "recipient_email.on_hold as email_on_hold";
        $query->_element['email_on_hold'] = 1;
        $query->_tables['recipient_email'] = $query->_whereTables['recipient_email'] = 1;
      }

      // get recipient email
      if (!empty($query->_returnProperties['email'])) {
        $query->_select['email'] = "recipient_email.email as email";
        $query->_element['email'] = 1;
        $query->_tables['recipient_email'] = $query->_whereTables['recipient_email'] = 1;
      }

      // get user opt out
      if (!empty($query->_returnProperties['contact_opt_out'])) {
        $query->_select['contact_opt_out'] = "contact_a.is_opt_out as contact_opt_out";
        $query->_element['contact_opt_out'] = 1;
      }

      // mailing job end date / completed date
      if (!empty($query->_returnProperties['mailing_job_end_date'])) {
        $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job']
          = " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
        $query->_select['mailing_job_end_date'] = "civicrm_mailing_job.end_date as mailing_job_end_date";
        $query->_element['mailing_job_end_date'] = 1;
      }

      if (!empty($query->_returnProperties['mailing_recipients_id'])) {
        $query->_select['mailing_recipients_id'] = " civicrm_mailing_recipients.id as mailing_recipients_id";
        $query->_element['mailing_recipients_id'] = 1;
      }
    }
  }

  /**
   * @param $query
   */
  public static function where(&$query) {
    $grouping = NULL;
    foreach (array_keys($query->_params) as $id) {
      if (empty($query->_params[$id][0])) {
        continue;
      }
      if (substr($query->_params[$id][0], 0, 8) == 'mailing_') {
        if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
          $query->_useDistinct = TRUE;
        }
        $grouping = $query->_params[$id][3];
        self::whereClauseSingle($query->_params[$id], $query);
      }
    }
  }

  /**
   * @param string $name
   * @param $mode
   * @param $side
   *
   * @return null|string
   */
  public static function from($name, $mode, $side) {
    $from = NULL;

    switch ($name) {
      case 'civicrm_mailing_recipients':
        $from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id";
        break;

      case 'civicrm_mailing_event_queue':
        // this is tightly binded so as to do a check WRT actual job recipients ('child' type jobs)
        $from = " INNER JOIN civicrm_mailing_event_queue ON
          civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
          AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'";
        break;

      case 'civicrm_mailing':
        $from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id ";
        break;

      case 'civicrm_mailing_job':
        $from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.is_test != 1 ";
        break;

      case 'civicrm_mailing_event_bounce':
      case 'civicrm_mailing_event_delivered':
      case 'civicrm_mailing_event_opened':
      case 'civicrm_mailing_event_reply':
      case 'civicrm_mailing_event_unsubscribe':
      case 'civicrm_mailing_event_forward':
      case 'civicrm_mailing_event_trackable_url_open':
        $from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id";
        break;

      case 'recipient_email':
        $from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_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_MAILING) {
      $properties = array(
        'mailing_id' => 1,
        'mailing_name' => 1,
        'sort_name' => 1,
        'email' => 1,
        'mailing_subject' => 1,
        'email_on_hold' => 1,
        'contact_opt_out' => 1,
        'mailing_job_status' => 1,
        'mailing_job_end_date' => 1,
        'contact_type' => 1,
        'contact_sub_type' => 1,
        'mailing_recipients_id' => 1,
      );
    }
    return $properties;
  }

  /**
   * @param $values
   * @param $query
   */
  public static function whereClauseSingle(&$values, &$query) {
    list($name, $op, $value, $grouping, $wildcard) = $values;

    $fields = array();
    $fields = self::getFields();

    switch ($name) {
      case 'mailing_id':
        $selectedMailings = array_flip($value);
        $value = "(" . implode(',', $value) . ")";
        $op = 'IN';
        $query->_where[$grouping][] = "civicrm_mailing.id $op $value";

        $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
        foreach ($selectedMailings as $id => $dnc) {
          $selectedMailings[$id] = $mailings[$id];
        }
        $selectedMailings = implode(' or ', $selectedMailings);

        $query->_qill[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
        $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
        $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
        return;

      case 'mailing_name':
        $value = strtolower(addslashes($value));
        if ($wildcard) {
          $value = "%$value%";
          $op = 'LIKE';
        }
        $query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'";
        $query->_qill[$grouping][] = "Mailing Namename $op \"$value\"";
        $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
        $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
        return;

      case 'mailing_date':
      case 'mailing_date_low':
      case 'mailing_date_high':
        // process to / from date
        $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
        $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
        $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
        $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
        $query->dateQueryBuilder($values,
          'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
        );
        return;

      case 'mailing_delivery_status':
        $options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered');

        list($name, $op, $value, $grouping, $wildcard) = $values;
        if ($value == 'Y') {
          self::mailingEventQueryBuilder($query, $values,
            'civicrm_mailing_event_delivered',
            'mailing_delivery_status',
            ts('Mailing Delivery'),
            $options
          );
        }
        elseif ($value == 'N') {
          $options['Y'] = $options['N'];
          $values = array($name, $op, 'Y', $grouping, $wildcard);
          self::mailingEventQueryBuilder($query, $values,
            'civicrm_mailing_event_bounce',
            'mailing_delivery_status',
            ts('Mailing Delivery'),
            $options
          );
        }
        return;

      case 'mailing_bounce_types':
        $op = 'IN';
        $values = array($name, $op, $value, $grouping, $wildcard);
        self::mailingEventQueryBuilder($query, $values,
          'civicrm_mailing_event_bounce',
          'bounce_type_id',
          ts('Bounce type(s)'),
          CRM_Core_PseudoConstant::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array(
              'keyColumn' => 'id',
              'labelColumn' => 'name',
            ))
        );
        return;

      case 'mailing_open_status':
        self::mailingEventQueryBuilder($query, $values,
          'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')
        );
        return;

      case 'mailing_click_status':
        self::mailingEventQueryBuilder($query, $values,
          'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click')
        );
        return;

      case 'mailing_reply_status':
        self::mailingEventQueryBuilder($query, $values,
          'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply')
        );
        return;

      case 'mailing_optout':
        $valueTitle = array(1 => ts('Opt-out Requests'));
        // include opt-out events only
        $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
        self::mailingEventQueryBuilder($query, $values,
          'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
          ts('Mailing: '), $valueTitle
        );
        return;

      case 'mailing_unsubscribe':
        $valueTitle = array(1 => ts('Unsubscribe Requests'));
        // exclude opt-out events
        $query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
        self::mailingEventQueryBuilder($query, $values,
          'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
          ts('Mailing: '), $valueTitle
        );
        return;

      case 'mailing_forward':
        $valueTitle = array('Y' => ts('Forwards'));
        // since its a checkbox
        $values[2] = 'Y';
        self::mailingEventQueryBuilder($query, $values,
          'civicrm_mailing_event_forward', 'mailing_forward',
          ts('Mailing: '), $valueTitle
        );
        return;

      case 'mailing_job_status':
        if (!empty($value)) {
          if ($value != 'Scheduled' && $value != 'Canceled') {
            $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
          }
          $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
          $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
          $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;

          $query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
          $query->_qill[$grouping][] = "Mailing Job Status IS \"$value\"";
        }
        return;
    }
  }

  /**
   * Add all the elements shared between Mailing search and advnaced search.
   *
   *
   * @param CRM_Core_Form $form
   * @return void
   */
  public static function buildSearchForm(&$form) {
    // mailing selectors
    $mailings = CRM_Mailing_BAO_Mailing::getMailingsList();

    if (!empty($mailings)) {
      $form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
        array('id' => 'mailing_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
      );
    }

    CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);

    $mailingJobStatuses = array(
      '' => ts('- select -'),
      'Complete' => 'Complete',
      'Scheduled' => 'Scheduled',
      'Running' => 'Running',
      'Canceled' => 'Canceled',
    );
    $form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);

    $mailingBounceTypes = CRM_Core_PseudoConstant::get(
      'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
      array('keyColumn' => 'id', 'labelColumn' => 'name')
    );
    $form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
      array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'class' => 'crm-select2')
    );

    // event filters
    $form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered'), array('allowClear' => TRUE));
    $form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open'), array('allowClear' => TRUE));
    $form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant::yesNoOptions('click'), array('allowClear' => TRUE));
    $form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply'), array('allowClear' => TRUE));

    $form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
    $form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
    $form->add('checkbox', 'mailing_forward', ts('Forwards'));

    $form->assign('validCiviMailing', TRUE);
  }

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

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

  /**
   * Filter query results based on which contacts do (not) have a particular mailing event in their history.
   *
   * @param $query
   * @param $values
   * @param string $tableName
   * @param string $fieldName
   * @param $fieldTitle
   *
   * @param $valueTitles
   *
   * @return void
   */
  public static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
    list($name, $op, $value, $grouping, $wildcard) = $values;

    if (empty($value) || $value == 'A') {
      // don't do any filtering
      return;
    }

    if ($value == 'Y') {
      $query->_where[$grouping][] = $tableName . ".id is not null ";
    }
    elseif ($value == 'N') {
      $query->_where[$grouping][] = $tableName . ".id is null ";
    }

    if (is_array($value)) {
      $query->_where[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
      $query->_qill[$grouping][] = "$fieldTitle $op " . implode(', ', array_intersect_key($valueTitles, array_flip($value)));
    }
    else {
      $query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
    }

    $query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
    $query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
    $query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
    $query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
    $query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
  }

}
Commit	Line	Data
6a488035 TO	1	<?php
	2	/*
	3	+--------------------------------------------------------------------+
39de6fd5	4	\| CiviCRM version 4.6 \|
6a488035	5	+--------------------------------------------------------------------+
06b69b18	6	\| Copyright CiviCRM LLC (c) 2004-2014 \|
6a488035 TO	7	+--------------------------------------------------------------------+
	8	\| This file is a part of CiviCRM. \|
	9	\| \|
	10	\| CiviCRM is free software; you can copy, modify, and distribute it \|
	11	\| under the terms of the GNU Affero General Public License \|
	12	\| Version 3, 19 November 2007. \|
	13	\| \|
	14	\| CiviCRM is distributed in the hope that it will be useful, but \|
	15	\| WITHOUT ANY WARRANTY; without even the implied warranty of \|
	16	\| MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. \|
	17	\| See the GNU Affero General Public License for more details. \|
	18	\| \|
	19	\| You should have received a copy of the GNU Affero General Public \|
	20	\| License along with this program; if not, contact CiviCRM LLC \|
	21	\| at info[AT]civicrm[DOT]org. If you have questions about the \|
	22	\| GNU Affero General Public License or the licensing of CiviCRM, \|
	23	\| see the CiviCRM license FAQ at http://civicrm.org/licensing \|
	24	+--------------------------------------------------------------------+
	25	*/
	26
	27	/**
	28	*
	29	* @package CRM
06b69b18	30	* @copyright CiviCRM LLC (c) 2004-2014
6a488035 TO	31	* $Id$
	32	*
	33	*/
	34	class CRM_Mailing_BAO_Query {
	35
	36	static $_mailingFields = NULL;
	37
e0ef6999 EM	38	/**
	39	* @return array\|null
	40	*/
00be9182	41	public static function &getFields() {
6a488035 TO	42	if (!self::$_mailingFields) {
	43	self::$_mailingFields = array();
	44	$_mailingFields['mailing_id'] = array(
	45	'name' => 'mailing_id',
	46	'title' => 'Mailing ID',
	47	'where' => 'civicrm_mailing.id',
	48	);
	49	}
	50	return self::$_mailingFields;
	51	}
	52
	53	/**
100fef9d	54	* If mailings are involved, add the specific Mailing fields
6a488035	55	*
77b97be7 EM	56	* @param $query
77b97be7 EM	57	*
6a488035	58	* @return void
6a488035	59	*/
00be9182	60	public static function select(&$query) {
6a488035 TO	61	// if Mailing mode add mailing id
	62	if ($query->_mode & CRM_Contact_BAO_Query::MODE_MAILING) {
	63	$query->_select['mailing_id'] = "civicrm_mailing.id as mailing_id";
	64	$query->_element['mailing_id'] = 1;
2cc569f2	65
335038eb	66	// base table is contact, so join recipients to it
6c552737 TO	67	$query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients']
6c552737 TO	68	= " INNER JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id ";
2cc569f2	69
335038eb	70	$query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
2cc569f2 PJ	71
2cc569f2 PJ	72	// get mailing name
a7488080	73	if (!empty($query->_returnProperties['mailing_name'])) {
353ffa53	74	$query->_select['mailing_name'] = "civicrm_mailing.name as mailing_name";
2cc569f2 PJ	75	$query->_element['mailing_name'] = 1;
	76	}
	77
	78	// get mailing subject
a7488080	79	if (!empty($query->_returnProperties['mailing_subject'])) {
353ffa53	80	$query->_select['mailing_subject'] = "civicrm_mailing.subject as mailing_subject";
2cc569f2 PJ	81	$query->_element['mailing_subject'] = 1;
	82	}
	83
	84	// get mailing status
a7488080	85	if (!empty($query->_returnProperties['mailing_job_status'])) {
6c552737 TO	86	$query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job']
6c552737 TO	87	= " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
353ffa53	88	$query->_select['mailing_job_status'] = "civicrm_mailing_job.status as mailing_job_status";
2cc569f2 PJ	89	$query->_element['mailing_job_status'] = 1;
	90	}
	91
	92	// get email on hold
a7488080	93	if (!empty($query->_returnProperties['email_on_hold'])) {
2cc569f2 PJ	94	$query->_select['email_on_hold'] = "recipient_email.on_hold as email_on_hold";
	95	$query->_element['email_on_hold'] = 1;
	96	$query->_tables['recipient_email'] = $query->_whereTables['recipient_email'] = 1;
	97	}
	98
	99	// get recipient email
a7488080	100	if (!empty($query->_returnProperties['email'])) {
2cc569f2 PJ	101	$query->_select['email'] = "recipient_email.email as email";
	102	$query->_element['email'] = 1;
	103	$query->_tables['recipient_email'] = $query->_whereTables['recipient_email'] = 1;
	104	}
	105
	106	// get user opt out
a7488080	107	if (!empty($query->_returnProperties['contact_opt_out'])) {
2cc569f2 PJ	108	$query->_select['contact_opt_out'] = "contact_a.is_opt_out as contact_opt_out";
	109	$query->_element['contact_opt_out'] = 1;
	110	}
	111
	112	// mailing job end date / completed date
a7488080	113	if (!empty($query->_returnProperties['mailing_job_end_date'])) {
6c552737 TO	114	$query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job']
6c552737 TO	115	= " LEFT JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.parent_id IS NULL AND civicrm_mailing_job.is_test != 1 ";
353ffa53	116	$query->_select['mailing_job_end_date'] = "civicrm_mailing_job.end_date as mailing_job_end_date";
2cc569f2 PJ	117	$query->_element['mailing_job_end_date'] = 1;
2cc569f2 PJ	118	}
47546fcf	119
a7488080	120	if (!empty($query->_returnProperties['mailing_recipients_id'])) {
335038eb PJ	121	$query->_select['mailing_recipients_id'] = " civicrm_mailing_recipients.id as mailing_recipients_id";
335038eb PJ	122	$query->_element['mailing_recipients_id'] = 1;
47546fcf	123	}
6a488035 TO	124	}
	125	}
	126
e0ef6999 EM	127	/**
	128	* @param $query
	129	*/
00be9182	130	public static function where(&$query) {
6a488035 TO	131	$grouping = NULL;
6a488035 TO	132	foreach (array_keys($query->_params) as $id) {
a7488080	133	if (empty($query->_params[$id][0])) {
6a488035 TO	134	continue;
	135	}
	136	if (substr($query->_params[$id][0], 0, 8) == 'mailing_') {
	137	if ($query->_mode == CRM_Contact_BAO_QUERY::MODE_CONTACTS) {
	138	$query->_useDistinct = TRUE;
	139	}
	140	$grouping = $query->_params[$id][3];
	141	self::whereClauseSingle($query->_params[$id], $query);
	142	}
	143	}
	144	}
	145
e0ef6999	146	/**
100fef9d	147	* @param string $name
e0ef6999 EM	148	* @param $mode
	149	* @param $side
	150	*
	151	* @return null\|string
	152	*/
00be9182	153	public static function from($name, $mode, $side) {
6a488035	154	$from = NULL;
335038eb	155
6a488035	156	switch ($name) {
335038eb PJ	157	case 'civicrm_mailing_recipients':
335038eb PJ	158	$from = " $side JOIN civicrm_mailing_recipients ON civicrm_mailing_recipients.contact_id = contact_a.id";
6a488035 TO	159	break;
6a488035 TO	160
335038eb PJ	161	case 'civicrm_mailing_event_queue':
	162	// this is tightly binded so as to do a check WRT actual job recipients ('child' type jobs)
	163	$from = " INNER JOIN civicrm_mailing_event_queue ON
	164	civicrm_mailing_event_queue.contact_id = civicrm_mailing_recipients.contact_id
	165	AND civicrm_mailing_event_queue.job_id = civicrm_mailing_job.id AND civicrm_mailing_job.job_type = 'child'";
6a488035 TO	166	break;
	167
	168	case 'civicrm_mailing':
335038eb PJ	169	$from = " $side JOIN civicrm_mailing ON civicrm_mailing.id = civicrm_mailing_recipients.mailing_id ";
	170	break;
	171
	172	case 'civicrm_mailing_job':
eb6a4756	173	$from = " $side JOIN civicrm_mailing_job ON civicrm_mailing_job.mailing_id = civicrm_mailing.id AND civicrm_mailing_job.is_test != 1 ";
6a488035 TO	174	break;
	175
	176	case 'civicrm_mailing_event_bounce':
	177	case 'civicrm_mailing_event_delivered':
	178	case 'civicrm_mailing_event_opened':
	179	case 'civicrm_mailing_event_reply':
	180	case 'civicrm_mailing_event_unsubscribe':
	181	case 'civicrm_mailing_event_forward':
	182	case 'civicrm_mailing_event_trackable_url_open':
	183	$from = " $side JOIN $name ON $name.event_queue_id = civicrm_mailing_event_queue.id";
	184	break;
2cc569f2 PJ	185
2cc569f2 PJ	186	case 'recipient_email':
335038eb PJ	187	$from = " $side JOIN civicrm_email recipient_email ON recipient_email.id = civicrm_mailing_recipients.email_id";
335038eb PJ	188	break;
6a488035 TO	189	}
	190
	191	return $from;
	192	}
	193
e0ef6999 EM	194	/**
	195	* @param $mode
	196	* @param bool $includeCustomFields
	197	*
	198	* @return array\|null
	199	*/
2da40d21	200	public static function defaultReturnProperties(
a3d7e8ee	201	$mode,
6a488035 TO	202	$includeCustomFields = TRUE
	203	) {
	204
	205	$properties = NULL;
	206	if ($mode & CRM_Contact_BAO_Query::MODE_MAILING) {
2cc569f2 PJ	207	$properties = array(
	208	'mailing_id' => 1,
	209	'mailing_name' => 1,
	210	'sort_name' => 1,
	211	'email' => 1,
	212	'mailing_subject' => 1,
	213	'email_on_hold' => 1,
	214	'contact_opt_out' => 1,
	215	'mailing_job_status' => 1,
	216	'mailing_job_end_date' => 1,
	217	'contact_type' => 1,
47546fcf	218	'contact_sub_type' => 1,
21dfd5f5	219	'mailing_recipients_id' => 1,
2cc569f2	220	);
6a488035 TO	221	}
	222	return $properties;
	223	}
	224
e0ef6999 EM	225	/**
	226	* @param $values
	227	* @param $query
	228	*/
00be9182	229	public static function whereClauseSingle(&$values, &$query) {
6a488035 TO	230	list($name, $op, $value, $grouping, $wildcard) = $values;
	231
	232	$fields = array();
	233	$fields = self::getFields();
335038eb	234
6a488035 TO	235	switch ($name) {
	236	case 'mailing_id':
	237	$selectedMailings = array_flip($value);
6a488035 TO	238	$value = "(" . implode(',', $value) . ")";
	239	$op = 'IN';
	240	$query->_where[$grouping][] = "civicrm_mailing.id $op $value";
	241
	242	$mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
	243	foreach ($selectedMailings as $id => $dnc) {
	244	$selectedMailings[$id] = $mailings[$id];
	245	}
	246	$selectedMailings = implode(' or ', $selectedMailings);
	247
	248	$query->_qill[$grouping][] = "Mailing Name $op \"$selectedMailings\"";
6a488035	249	$query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
335038eb	250	$query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
6a488035 TO	251	return;
	252
	253	case 'mailing_name':
481a74f4 TO	254	$value = strtolower(addslashes($value));
481a74f4 TO	255	if ($wildcard) {
6a488035	256	$value = "%$value%";
353ffa53	257	$op = 'LIKE';
6a488035 TO	258	}
6a488035 TO	259	$query->_where[$grouping][] = "LOWER(civicrm_mailing.name) $op '$value'";
353ffa53	260	$query->_qill[$grouping][] = "Mailing Namename $op \"$value\"";
6a488035	261	$query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
335038eb	262	$query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
6a488035 TO	263	return;
	264
	265	case 'mailing_date':
	266	case 'mailing_date_low':
	267	case 'mailing_date_high':
	268	// process to / from date
335038eb	269	$query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
6a488035 TO	270	$query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
6a488035 TO	271	$query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
335038eb	272	$query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
6a488035 TO	273	$query->dateQueryBuilder($values,
	274	'civicrm_mailing_job', 'mailing_date', 'start_date', 'Mailing Delivery Date'
	275	);
	276	return;
	277
	278	case 'mailing_delivery_status':
	279	$options = CRM_Mailing_PseudoConstant::yesNoOptions('delivered');
	280
	281	list($name, $op, $value, $grouping, $wildcard) = $values;
	282	if ($value == 'Y') {
	283	self::mailingEventQueryBuilder($query, $values,
	284	'civicrm_mailing_event_delivered',
	285	'mailing_delivery_status',
	286	ts('Mailing Delivery'),
	287	$options
	288	);
	289	}
	290	elseif ($value == 'N') {
	291	$options['Y'] = $options['N'];
	292	$values = array($name, $op, 'Y', $grouping, $wildcard);
	293	self::mailingEventQueryBuilder($query, $values,
	294	'civicrm_mailing_event_bounce',
	295	'mailing_delivery_status',
	296	ts('Mailing Delivery'),
	297	$options
	298	);
	299	}
	300	return;
	301
111b2847	302	case 'mailing_bounce_types':
	303	$op = 'IN';
	304	$values = array($name, $op, $value, $grouping, $wildcard);
	305	self::mailingEventQueryBuilder($query, $values,
	306	'civicrm_mailing_event_bounce',
	307	'bounce_type_id',
	308	ts('Bounce type(s)'),
353ffa53 TO	309	CRM_Core_PseudoConstant::get('CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id', array(
353ffa53 TO	310	'keyColumn' => 'id',
af9b09df	311	'labelColumn' => 'name',
353ffa53	312	))
111b2847	313	);
	314	return;
	315
6a488035 TO	316	case 'mailing_open_status':
	317	self::mailingEventQueryBuilder($query, $values,
	318	'civicrm_mailing_event_opened', 'mailing_open_status', ts('Mailing: Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open')
	319	);
	320	return;
	321
	322	case 'mailing_click_status':
	323	self::mailingEventQueryBuilder($query, $values,
	324	'civicrm_mailing_event_trackable_url_open', 'mailing_click_status', ts('Mailing: Trackable URL Clicks'), CRM_Mailing_PseudoConstant::yesNoOptions('click')
	325	);
	326	return;
	327
	328	case 'mailing_reply_status':
	329	self::mailingEventQueryBuilder($query, $values,
	330	'civicrm_mailing_event_reply', 'mailing_reply_status', ts('Mailing: Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply')
	331	);
	332	return;
	333
	334	case 'mailing_optout':
	335	$valueTitle = array(1 => ts('Opt-out Requests'));
	336	// include opt-out events only
	337	$query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 1";
	338	self::mailingEventQueryBuilder($query, $values,
	339	'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
	340	ts('Mailing: '), $valueTitle
	341	);
	342	return;
	343
	344	case 'mailing_unsubscribe':
	345	$valueTitle = array(1 => ts('Unsubscribe Requests'));
	346	// exclude opt-out events
	347	$query->_where[$grouping][] = "civicrm_mailing_event_unsubscribe.org_unsubscribe = 0";
	348	self::mailingEventQueryBuilder($query, $values,
	349	'civicrm_mailing_event_unsubscribe', 'mailing_unsubscribe',
	350	ts('Mailing: '), $valueTitle
	351	);
	352	return;
	353
	354	case 'mailing_forward':
	355	$valueTitle = array('Y' => ts('Forwards'));
	356	// since its a checkbox
	357	$values[2] = 'Y';
	358	self::mailingEventQueryBuilder($query, $values,
	359	'civicrm_mailing_event_forward', 'mailing_forward',
	360	ts('Mailing: '), $valueTitle
	361	);
	362	return;
335038eb PJ	363
	364	case 'mailing_job_status':
	365	if (!empty($value)) {
	366	if ($value != 'Scheduled' && $value != 'Canceled') {
	367	$query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
	368	}
	369	$query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
	370	$query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
	371	$query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
	372
	373	$query->_where[$grouping][] = " civicrm_mailing_job.status = '{$value}' ";
353ffa53	374	$query->_qill[$grouping][] = "Mailing Job Status IS \"$value\"";
335038eb PJ	375	}
335038eb PJ	376	return;
6a488035 TO	377	}
	378	}
	379
	380	/**
fe482240	381	* Add all the elements shared between Mailing search and advnaced search.
6a488035	382	*
6a488035	383	*
100fef9d	384	* @param CRM_Core_Form $form
6a488035	385	* @return void
6a488035	386	*/
00be9182	387	public static function buildSearchForm(&$form) {
6a488035 TO	388	// mailing selectors
	389	$mailings = CRM_Mailing_BAO_Mailing::getMailingsList();
	390
	391	if (!empty($mailings)) {
	392	$form->add('select', 'mailing_id', ts('Mailing Name(s)'), $mailings, FALSE,
ab345ca5	393	array('id' => 'mailing_id', 'multiple' => 'multiple', 'class' => 'crm-select2')
6a488035 TO	394	);
	395	}
	396
bc3f7f04	397	CRM_Core_Form_Date::buildDateRange($form, 'mailing_date', 1, '_low', '_high', ts('From'), FALSE);
6a488035	398
335038eb PJ	399	$mailingJobStatuses = array(
	400	'' => ts('- select -'),
	401	'Complete' => 'Complete',
	402	'Scheduled' => 'Scheduled',
	403	'Running' => 'Running',
21dfd5f5	404	'Canceled' => 'Canceled',
335038eb PJ	405	);
	406	$form->addElement('select', 'mailing_job_status', ts('Mailing Job Status'), $mailingJobStatuses, FALSE);
	407
111b2847	408	$mailingBounceTypes = CRM_Core_PseudoConstant::get(
	409	'CRM_Mailing_Event_DAO_Bounce', 'bounce_type_id',
	410	array('keyColumn' => 'id', 'labelColumn' => 'name')
	411	);
c9fd7217	412	$form->add('select', 'mailing_bounce_types', ts('Bounce Types'), $mailingBounceTypes, FALSE,
ab345ca5	413	array('id' => 'mailing_bounce_types', 'multiple' => 'multiple', 'class' => 'crm-select2')
c9fd7217	414	);
111b2847	415
6a488035	416	// event filters
b847e6e7 CW	417	$form->addRadio('mailing_delivery_status', ts('Delivery Status'), CRM_Mailing_PseudoConstant::yesNoOptions('delivered'), array('allowClear' => TRUE));
	418	$form->addRadio('mailing_open_status', ts('Trackable Opens'), CRM_Mailing_PseudoConstant::yesNoOptions('open'), array('allowClear' => TRUE));
	419	$form->addRadio('mailing_click_status', ts('Trackable URLs'), CRM_Mailing_PseudoConstant::yesNoOptions('click'), array('allowClear' => TRUE));
	420	$form->addRadio('mailing_reply_status', ts('Trackable Replies'), CRM_Mailing_PseudoConstant::yesNoOptions('reply'), array('allowClear' => TRUE));
6a488035 TO	421
	422	$form->add('checkbox', 'mailing_unsubscribe', ts('Unsubscribe Requests'));
	423	$form->add('checkbox', 'mailing_optout', ts('Opt-out Requests'));
	424	$form->add('checkbox', 'mailing_forward', ts('Forwards'));
	425
	426	$form->assign('validCiviMailing', TRUE);
6a488035 TO	427	}
6a488035 TO	428
e0ef6999 EM	429	/**
e0ef6999 EM	430	* @param $row
100fef9d	431	* @param int $id
e0ef6999	432	*/
35f7561f TO	433	public static function searchAction(&$row, $id) {
35f7561f TO	434	}
6a488035	435
e0ef6999 EM	436	/**
	437	* @param $tables
	438	*/
00be9182	439	public static function tableNames(&$tables) {
337750c9	440	}
6a488035 TO	441
	442	/**
	443	* Filter query results based on which contacts do (not) have a particular mailing event in their history.
	444	*
	445	* @param $query
	446	* @param $values
100fef9d CW	447	* @param string $tableName
100fef9d CW	448	* @param string $fieldName
6a488035 TO	449	* @param $fieldTitle
6a488035 TO	450	*
77b97be7 EM	451	* @param $valueTitles
77b97be7 EM	452	*
6a488035 TO	453	* @return void
6a488035 TO	454	*/
00be9182	455	public static function mailingEventQueryBuilder(&$query, &$values, $tableName, $fieldName, $fieldTitle, &$valueTitles) {
6a488035 TO	456	list($name, $op, $value, $grouping, $wildcard) = $values;
	457
	458	if (empty($value) \|\| $value == 'A') {
	459	// don't do any filtering
	460	return;
	461	}
	462
	463	if ($value == 'Y') {
	464	$query->_where[$grouping][] = $tableName . ".id is not null ";
	465	}
	466	elseif ($value == 'N') {
	467	$query->_where[$grouping][] = $tableName . ".id is null ";
	468	}
	469
111b2847	470	if (is_array($value)) {
111b2847	471	$query->_where[$grouping][] = "$tableName.$fieldName $op (" . implode(',', $value) . ")";
86bfa4f6	472	$query->_qill[$grouping][] = "$fieldTitle $op " . implode(', ', array_intersect_key($valueTitles, array_flip($value)));
111b2847	473	}
	474	else {
	475	$query->_qill[$grouping][] = $fieldTitle . ' - ' . $valueTitles[$value];
	476	}
	477
dd879954	478	$query->_tables['civicrm_mailing'] = $query->_whereTables['civicrm_mailing'] = 1;
335038eb	479	$query->_tables['civicrm_mailing_job'] = $query->_whereTables['civicrm_mailing_job'] = 1;
6a488035	480	$query->_tables['civicrm_mailing_event_queue'] = $query->_whereTables['civicrm_mailing_event_queue'] = 1;
335038eb	481	$query->_tables['civicrm_mailing_recipients'] = $query->_whereTables['civicrm_mailing_recipients'] = 1;
6a488035 TO	482	$query->_tables[$tableName] = $query->_whereTables[$tableName] = 1;
6a488035 TO	483	}
96025800	484
6a488035	485	}