[civicrm-core.git] / CRM / Contact / Form / Search / Custom / DateAdded.php

<?php
/*
 +--------------------------------------------------------------------+
 | CiviCRM version 5                                                  |
 +--------------------------------------------------------------------+
 | Copyright CiviCRM LLC (c) 2004-2018                                |
 +--------------------------------------------------------------------+
 | 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-2018
 */
class CRM_Contact_Form_Search_Custom_DateAdded extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {

  protected $_debug = 0;
  protected $_aclFrom = NULL;
  protected $_aclWhere = NULL;

  /**
   * Class constructor.
   *
   * @param array $formValues
   */
  public function __construct(&$formValues) {
    parent::__construct($formValues);

    $this->_includeGroups = CRM_Utils_Array::value('includeGroups', $formValues, array());
    $this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $formValues, array());

    $this->_columns = array(
      ts('Contact ID') => 'contact_id',
      ts('Contact Type') => 'contact_type',
      ts('Name') => 'sort_name',
      ts('Date Added') => 'date_added',
    );
  }

  /**
   * @param CRM_Core_Form $form
   */
  public function buildForm(&$form) {
    $form->addDate('start_date', ts('Start Date'), FALSE, array('formatType' => 'custom'));
    $form->addDate('end_date', ts('End Date'), FALSE, array('formatType' => 'custom'));

    $groups = CRM_Core_PseudoConstant::nestedGroup();

    $select2style = array(
      'multiple' => TRUE,
      'style' => 'width: 100%; max-width: 60em;',
      'class' => 'crm-select2',
      'placeholder' => ts('- select -'),
    );

    $form->add('select', 'includeGroups',
      ts('Include Group(s)'),
      $groups,
      FALSE,
      $select2style
    );

    $form->add('select', 'excludeGroups',
      ts('Exclude Group(s)'),
      $groups,
      FALSE,
      $select2style
    );

    $this->setTitle('Search by date added to CiviCRM');

    //redirect if group not available for search criteria
    if (count($groups) == 0) {
      CRM_Core_Error::statusBounce(ts("Atleast one Group must be present for search."),
        CRM_Utils_System::url('civicrm/contact/search/custom/list',
          'reset=1'
        )
      );
    }

    /**
     * if you are using the standard template, this array tells the template what elements
     * are part of the search criteria
     */
    $form->assign('elements', array('start_date', 'end_date', 'includeGroups', 'excludeGroups'));
  }

  /**
   * @return null
   */
  public function summary() {
    return NULL;
  }

  /**
   * @param int $offset
   * @param int $rowcount
   * @param null $sort
   * @param bool $returnSQL
   *
   * @return string
   */
  public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
    return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
  }

  /**
   * @param int $offset
   * @param int $rowcount
   * @param null $sort
   * @param bool $includeContactIDs
   * @param bool $justIDs
   *
   * @return string
   */
  public function all(
    $offset = 0, $rowcount = 0, $sort = NULL,
    $includeContactIDs = FALSE, $justIDs = FALSE
  ) {

    $this->_includeGroups = CRM_Utils_Array::value('includeGroups', $this->_formValues, array());

    $this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $this->_formValues, array());

    $this->_allSearch = FALSE;
    $this->_groups = FALSE;

    if (empty($this->_includeGroups) && empty($this->_excludeGroups)) {
      //empty search
      $this->_allSearch = TRUE;
    }

    if (!empty($this->_includeGroups) || !empty($this->_excludeGroups)) {
      //group(s) selected
      $this->_groups = TRUE;
    }

    if ($justIDs) {
      $selectClause = "contact_a.id as contact_id";
      $groupBy = " GROUP BY contact_a.id";
      $sort = "contact_a.id";
    }
    else {
      $selectClause = "contact_a.id  as contact_id,
                       contact_a.contact_type as contact_type,
                       contact_a.sort_name    as sort_name,
                      d.date_added           as date_added";
      $groupBy = " GROUP BY contact_id ";
    }

    return $this->sql($selectClause,
      $offset, $rowcount, $sort,
      $includeContactIDs, $groupBy
    );
  }

  /**
   * @return string
   */
  public function from() {
    //define table name
    $randomNum = md5(uniqid());
    $this->_tableName = "civicrm_temp_custom_{$randomNum}";

    //grab the contacts added in the date range first
    $sql = "CREATE TEMPORARY TABLE dates_{$this->_tableName} ( id int primary key, date_added date ) ENGINE=HEAP";
    if ($this->_debug > 0) {
      print "-- Date range query: <pre>";
      print "$sql;";
      print "</pre>";
    }
    CRM_Core_DAO::executeQuery($sql);

    $startDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['start_date']));
    $endDateFix = NULL;
    if (!empty($this->_formValues['end_date'])) {
      $endDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['end_date']));
      # tack 11:59pm on to make search inclusive of the end date
      $endDateFix = "AND date_added <= '" . substr($endDate, 0, 10) . " 23:59:00'";
    }

    $dateRange = "INSERT INTO dates_{$this->_tableName} ( id, date_added )
          SELECT
              civicrm_contact.id,
              min(civicrm_log.modified_date) AS date_added
          FROM
              civicrm_contact LEFT JOIN civicrm_log
              ON (civicrm_contact.id = civicrm_log.entity_id AND
                  civicrm_log.entity_table = 'civicrm_contact')
          GROUP BY
              civicrm_contact.id
          HAVING
              date_added >= '$startDate'
              $endDateFix";

    if ($this->_debug > 0) {
      print "-- Date range query: <pre>";
      print "$dateRange;";
      print "</pre>";
    }

    CRM_Core_DAO::executeQuery($dateRange, CRM_Core_DAO::$_nullArray);

    // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
    // CRM-6356
    if ($this->_groups) {
      //block for Group search
      $smartGroup = array();
      $group = new CRM_Contact_DAO_Group();
      $group->is_active = 1;
      $group->find();
      while ($group->fetch()) {
        $allGroups[] = $group->id;
        if ($group->saved_search_id) {
          $smartGroup[$group->saved_search_id] = $group->id;
        }
      }
      $includedGroups = implode(',', $allGroups);

      if (!empty($this->_includeGroups)) {
        $iGroups = implode(',', $this->_includeGroups);
      }
      else {
        //if no group selected search for all groups
        $iGroups = $includedGroups;
      }
      if (is_array($this->_excludeGroups)) {
        $xGroups = implode(',', $this->_excludeGroups);
      }
      else {
        $xGroups = 0;
      }

      $sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
      CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
      $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
      CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);

      //used only when exclude group is selected
      if ($xGroups != 0) {
        $excludeGroup = "INSERT INTO  Xg_{$this->_tableName} ( contact_id )
                  SELECT  DISTINCT civicrm_group_contact.contact_id
                  FROM civicrm_group_contact, dates_{$this->_tableName} AS d
                  WHERE
                     d.id = civicrm_group_contact.contact_id AND
                     civicrm_group_contact.status = 'Added' AND
                     civicrm_group_contact.group_id IN( {$xGroups})";

        CRM_Core_DAO::executeQuery($excludeGroup, CRM_Core_DAO::$_nullArray);

        //search for smart group contacts
        foreach ($this->_excludeGroups as $keys => $values) {
          if (in_array($values, $smartGroup)) {
            $ssId = CRM_Utils_Array::key($values, $smartGroup);

            $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);

            $smartSql = $smartSql . " AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";

            $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";

            CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray);
          }
        }
      }

      $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
      CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
      $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}
                ( id int PRIMARY KEY AUTO_INCREMENT,
                  contact_id int,
                  group_names varchar(64)) ENGINE=HEAP";

      if ($this->_debug > 0) {
        print "-- Include groups query: <pre>";
        print "$sql;";
        print "</pre>";
      }

      CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);

      $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
                 SELECT      d.id as contact_id, civicrm_group.name as group_name
                 FROM        dates_{$this->_tableName} AS d
                 INNER JOIN  civicrm_group_contact
                 ON          civicrm_group_contact.contact_id = d.id
                 LEFT JOIN   civicrm_group
                 ON          civicrm_group_contact.group_id = civicrm_group.id";

      //used only when exclude group is selected
      if ($xGroups != 0) {
        $includeGroup .= " LEFT JOIN        Xg_{$this->_tableName}
                                          ON        d.id = Xg_{$this->_tableName}.contact_id";
      }
      $includeGroup .= " WHERE
                                     civicrm_group_contact.status = 'Added'  AND
                                     civicrm_group_contact.group_id IN($iGroups)";

      //used only when exclude group is selected
      if ($xGroups != 0) {
        $includeGroup .= " AND  Xg_{$this->_tableName}.contact_id IS null";
      }

      if ($this->_debug > 0) {
        print "-- Include groups query: <pre>";
        print "$includeGroup;";
        print "</pre>";
      }

      CRM_Core_DAO::executeQuery($includeGroup, CRM_Core_DAO::$_nullArray);

      //search for smart group contacts
      foreach ($this->_includeGroups as $keys => $values) {
        if (in_array($values, $smartGroup)) {

          $ssId = CRM_Utils_Array::key($values, $smartGroup);

          $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);

          $smartSql .= " AND contact_a.id IN (
                                   SELECT id AS contact_id
                                   FROM dates_{$this->_tableName} )";

          $smartSql .= " AND contact_a.id NOT IN (
                                   SELECT contact_id FROM civicrm_group_contact
                                   WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";

          //used only when exclude group is selected
          if ($xGroups != 0) {
            $smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM  Xg_{$this->_tableName})";
          }

          $smartGroupQuery = " INSERT IGNORE INTO
                        Ig_{$this->_tableName}(contact_id)
                        $smartSql";

          CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray);
          if ($this->_debug > 0) {
            print "-- Smart group query: <pre>";
            print "$smartGroupQuery;";
            print "</pre>";
          }
          $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
                        SET group_names = (SELECT title FROM civicrm_group
                            WHERE civicrm_group.id = $values)
                        WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
                            AND Ig_{$this->_tableName}.group_names IS NULL";
          CRM_Core_DAO::executeQuery($insertGroupNameQuery, CRM_Core_DAO::$_nullArray);
          if ($this->_debug > 0) {
            print "-- Smart group query: <pre>";
            print "$insertGroupNameQuery;";
            print "</pre>";
          }
        }
      }
    }
    // end if( $this->_groups ) condition
    $this->buildACLClause('contact_a');
    $from = "FROM civicrm_contact contact_a";

    /* We need to join to this again to get the date_added value */

    $from .= " INNER JOIN dates_{$this->_tableName} d ON (contact_a.id = d.id) {$this->_aclFrom}";

    // Only include groups in the search query of one or more Include OR Exclude groups has been selected.
    // CRM-6356
    if ($this->_groups) {
      $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
    }

    return $from;
  }

  /**
   * @param bool $includeContactIDs
   *
   * @return string
   */
  public function where($includeContactIDs = FALSE) {
    $where = '(1)';
    if ($this->_aclWhere) {
      $where .= " AND {$this->_aclWhere} ";
    }
    return $where;
  }

  /**
   * @return string
   */
  public function templateFile() {
    return 'CRM/Contact/Form/Search/Custom.tpl';
  }

  /**
   * @param $title
   */
  public function setTitle($title) {
    if ($title) {
      CRM_Utils_System::setTitle($title);
    }
    else {
      CRM_Utils_System::setTitle(ts('Search'));
    }
  }

  /**
   * @return mixed
   */
  public function count() {
    $sql = $this->all();

    $dao = CRM_Core_DAO::executeQuery($sql,
      CRM_Core_DAO::$_nullArray
    );
    return $dao->N;
  }

  public function __destruct() {
    //drop the temp. tables if they exist
    if (!empty($this->_includeGroups)) {
      $sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
      CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
    }

    if (!empty($this->_excludeGroups)) {
      $sql = "DROP TEMPORARY TABLE IF EXISTS  Xg_{$this->_tableName}";
      CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
    }
  }

  /**
   * @param string $tableAlias
   */
  public function buildACLClause($tableAlias = 'contact') {
    list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
  }

}
Commit	Line	Data
6a488035 TO	1	<?php
	2	/*
	3	+--------------------------------------------------------------------+
fee14197	4	\| CiviCRM version 5 \|
6a488035	5	+--------------------------------------------------------------------+
8c9251b3	6	\| Copyright CiviCRM LLC (c) 2004-2018 \|
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 and the CiviCRM Licensing Exception. \|
	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 and the CiviCRM Licensing Exception along \|
	21	\| with this program; if not, contact CiviCRM LLC \|
	22	\| at info[AT]civicrm[DOT]org. If you have questions about the \|
	23	\| GNU Affero General Public License or the licensing of CiviCRM, \|
	24	\| see the CiviCRM license FAQ at http://civicrm.org/licensing \|
	25	+--------------------------------------------------------------------+
d25dd0ee	26	*/
6a488035 TO	27
	28	/**
	29	*
	30	* @package CRM
8c9251b3	31	* @copyright CiviCRM LLC (c) 2004-2018
6a488035 TO	32	*/
	33	class CRM_Contact_Form_Search_Custom_DateAdded extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {
	34
430ae6dd	35	protected $_debug = 0;
d14ccbdc SL	36	protected $_aclFrom = NULL;
d14ccbdc SL	37	protected $_aclWhere = NULL;
430ae6dd	38
86538308	39	/**
5a409b50	40	* Class constructor.
	41	*
	42	* @param array $formValues
86538308	43	*/
00be9182	44	public function __construct(&$formValues) {
6a488035 TO	45	parent::__construct($formValues);
	46
	47	$this->_includeGroups = CRM_Utils_Array::value('includeGroups', $formValues, array());
	48	$this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $formValues, array());
	49
	50	$this->_columns = array(
7b99ead3	51	ts('Contact ID') => 'contact_id',
6a488035 TO	52	ts('Contact Type') => 'contact_type',
	53	ts('Name') => 'sort_name',
	54	ts('Date Added') => 'date_added',
	55	);
	56	}
	57
86538308	58	/**
11cac306	59	* @param CRM_Core_Form $form
86538308	60	*/
00be9182	61	public function buildForm(&$form) {
6a488035 TO	62	$form->addDate('start_date', ts('Start Date'), FALSE, array('formatType' => 'custom'));
	63	$form->addDate('end_date', ts('End Date'), FALSE, array('formatType' => 'custom'));
	64
24431f7b	65	$groups = CRM_Core_PseudoConstant::nestedGroup();
11cac306 CW	66
	67	$select2style = array(
	68	'multiple' => TRUE,
	69	'style' => 'width: 100%; max-width: 60em;',
	70	'class' => 'crm-select2',
	71	'placeholder' => ts('- select -'),
6a488035 TO	72	);
6a488035 TO	73
11cac306 CW	74	$form->add('select', 'includeGroups',
	75	ts('Include Group(s)'),
	76	$groups,
	77	FALSE,
	78	$select2style
6a488035 TO	79	);
6a488035 TO	80
11cac306 CW	81	$form->add('select', 'excludeGroups',
	82	ts('Exclude Group(s)'),
	83	$groups,
	84	FALSE,
	85	$select2style
	86	);
6a488035 TO	87
	88	$this->setTitle('Search by date added to CiviCRM');
	89
	90	//redirect if group not available for search criteria
	91	if (count($groups) == 0) {
	92	CRM_Core_Error::statusBounce(ts("Atleast one Group must be present for search."),
	93	CRM_Utils_System::url('civicrm/contact/search/custom/list',
	94	'reset=1'
	95	)
	96	);
	97	}
	98
	99	/**
	100	* if you are using the standard template, this array tells the template what elements
	101	* are part of the search criteria
	102	*/
	103	$form->assign('elements', array('start_date', 'end_date', 'includeGroups', 'excludeGroups'));
	104	}
	105
86538308 EM	106	/**
	107	* @return null
	108	*/
00be9182	109	public function summary() {
6a488035 TO	110	return NULL;
	111	}
	112
cd5823ae EM	113	/**
	114	* @param int $offset
	115	* @param int $rowcount
	116	* @param null $sort
	117	* @param bool $returnSQL
	118	*
	119	* @return string
	120	*/
00be9182	121	public function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
26a9b6ab C	122	return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
	123	}
	124
86538308 EM	125	/**
	126	* @param int $offset
	127	* @param int $rowcount
	128	* @param null $sort
	129	* @param bool $includeContactIDs
	130	* @param bool $justIDs
	131	*
	132	* @return string
	133	*/
79d7553f	134	public function all(
51ccfbbe	135	$offset = 0, $rowcount = 0, $sort = NULL,
6a488035 TO	136	$includeContactIDs = FALSE, $justIDs = FALSE
	137	) {
	138
	139	$this->_includeGroups = CRM_Utils_Array::value('includeGroups', $this->_formValues, array());
	140
	141	$this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $this->_formValues, array());
	142
	143	$this->_allSearch = FALSE;
	144	$this->_groups = FALSE;
	145
	146	if (empty($this->_includeGroups) && empty($this->_excludeGroups)) {
	147	//empty search
	148	$this->_allSearch = TRUE;
	149	}
	150
	151	if (!empty($this->_includeGroups) \|\| !empty($this->_excludeGroups)) {
	152	//group(s) selected
	153	$this->_groups = TRUE;
	154	}
	155
	156	if ($justIDs) {
26a9b6ab C	157	$selectClause = "contact_a.id as contact_id";
	158	$groupBy = " GROUP BY contact_a.id";
	159	$sort = "contact_a.id";
6a488035 TO	160	}
	161	else {
	162	$selectClause = "contact_a.id as contact_id,
	163	contact_a.contact_type as contact_type,
	164	contact_a.sort_name as sort_name,
	165	d.date_added as date_added";
26a9b6ab	166	$groupBy = " GROUP BY contact_id ";
6a488035 TO	167	}
6a488035 TO	168
6a488035 TO	169	return $this->sql($selectClause,
	170	$offset, $rowcount, $sort,
	171	$includeContactIDs, $groupBy
	172	);
	173	}
	174
86538308 EM	175	/**
	176	* @return string
	177	*/
00be9182	178	public function from() {
6a488035 TO	179	//define table name
	180	$randomNum = md5(uniqid());
	181	$this->_tableName = "civicrm_temp_custom_{$randomNum}";
	182
	183	//grab the contacts added in the date range first
	184	$sql = "CREATE TEMPORARY TABLE dates_{$this->_tableName} ( id int primary key, date_added date ) ENGINE=HEAP";
	185	if ($this->_debug > 0) {
	186	print "-- Date range query: <pre>";
	187	print "$sql;";
	188	print "</pre>";
	189	}
	190	CRM_Core_DAO::executeQuery($sql);
	191
	192	$startDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['start_date']));
	193	$endDateFix = NULL;
	194	if (!empty($this->_formValues['end_date'])) {
	195	$endDate = CRM_Utils_Date::mysqlToIso(CRM_Utils_Date::processDate($this->_formValues['end_date']));
	196	# tack 11:59pm on to make search inclusive of the end date
	197	$endDateFix = "AND date_added <= '" . substr($endDate, 0, 10) . " 23:59:00'";
	198	}
	199
	200	$dateRange = "INSERT INTO dates_{$this->_tableName} ( id, date_added )
	201	SELECT
	202	civicrm_contact.id,
	203	min(civicrm_log.modified_date) AS date_added
	204	FROM
	205	civicrm_contact LEFT JOIN civicrm_log
	206	ON (civicrm_contact.id = civicrm_log.entity_id AND
	207	civicrm_log.entity_table = 'civicrm_contact')
	208	GROUP BY
	209	civicrm_contact.id
	210	HAVING
	211	date_added >= '$startDate'
	212	$endDateFix";
	213
	214	if ($this->_debug > 0) {
	215	print "-- Date range query: <pre>";
	216	print "$dateRange;";
	217	print "</pre>";
	218	}
	219
	220	CRM_Core_DAO::executeQuery($dateRange, CRM_Core_DAO::$_nullArray);
	221
	222	// Only include groups in the search query of one or more Include OR Exclude groups has been selected.
	223	// CRM-6356
	224	if ($this->_groups) {
	225	//block for Group search
353ffa53 TO	226	$smartGroup = array();
353ffa53 TO	227	$group = new CRM_Contact_DAO_Group();
6a488035 TO	228	$group->is_active = 1;
	229	$group->find();
	230	while ($group->fetch()) {
	231	$allGroups[] = $group->id;
	232	if ($group->saved_search_id) {
	233	$smartGroup[$group->saved_search_id] = $group->id;
	234	}
	235	}
	236	$includedGroups = implode(',', $allGroups);
	237
	238	if (!empty($this->_includeGroups)) {
	239	$iGroups = implode(',', $this->_includeGroups);
	240	}
	241	else {
	242	//if no group selected search for all groups
	243	$iGroups = $includedGroups;
	244	}
	245	if (is_array($this->_excludeGroups)) {
	246	$xGroups = implode(',', $this->_excludeGroups);
	247	}
	248	else {
	249	$xGroups = 0;
	250	}
	251
	252	$sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
	253	CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
	254	$sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=HEAP";
	255	CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
	256
	257	//used only when exclude group is selected
	258	if ($xGroups != 0) {
	259	$excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
	260	SELECT DISTINCT civicrm_group_contact.contact_id
	261	FROM civicrm_group_contact, dates_{$this->_tableName} AS d
	262	WHERE
	263	d.id = civicrm_group_contact.contact_id AND
	264	civicrm_group_contact.status = 'Added' AND
	265	civicrm_group_contact.group_id IN( {$xGroups})";
	266
	267	CRM_Core_DAO::executeQuery($excludeGroup, CRM_Core_DAO::$_nullArray);
	268
	269	//search for smart group contacts
	270	foreach ($this->_excludeGroups as $keys => $values) {
	271	if (in_array($values, $smartGroup)) {
	272	$ssId = CRM_Utils_Array::key($values, $smartGroup);
	273
	274	$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
	275
	276	$smartSql = $smartSql . " AND contact_a.id NOT IN (
	277	SELECT contact_id FROM civicrm_group_contact
	278	WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
	279
	280	$smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
	281
	282	CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray);
	283	}
	284	}
	285	}
	286
	287	$sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
	288	CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
	289	$sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName}
	290	( id int PRIMARY KEY AUTO_INCREMENT,
	291	contact_id int,
292	group_names varchar(64)) ENGINE=HEAP";
293
294	if ($this->_debug > 0) {
295	print "-- Include groups query: <pre>";
296	print "$sql;";
297	print "</pre>";
298	}
299
300	CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
301
302	$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
303	SELECT d.id as contact_id, civicrm_group.name as group_name
304	FROM dates_{$this->_tableName} AS d
305	INNER JOIN civicrm_group_contact
306	ON civicrm_group_contact.contact_id = d.id
307	LEFT JOIN civicrm_group
308	ON civicrm_group_contact.group_id = civicrm_group.id";
309
310	//used only when exclude group is selected
311	if ($xGroups != 0) {
312	$includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
313	ON d.id = Xg_{$this->_tableName}.contact_id";
314	}
315	$includeGroup .= " WHERE
316	civicrm_group_contact.status = 'Added' AND
317	civicrm_group_contact.group_id IN($iGroups)";
318
319	//used only when exclude group is selected
320	if ($xGroups != 0) {
321	$includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
322	}
323
324	if ($this->_debug > 0) {
325	print "-- Include groups query: <pre>";
326	print "$includeGroup;";
327	print "</pre>";
328	}
329
330	CRM_Core_DAO::executeQuery($includeGroup, CRM_Core_DAO::$_nullArray);
331
332	//search for smart group contacts
333	foreach ($this->_includeGroups as $keys => $values) {
334	if (in_array($values, $smartGroup)) {
335
336	$ssId = CRM_Utils_Array::key($values, $smartGroup);
337
338	$smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId);
339
340	$smartSql .= " AND contact_a.id IN (
341	SELECT id AS contact_id
342	FROM dates_{$this->_tableName} )";
343
344	$smartSql .= " AND contact_a.id NOT IN (
345	SELECT contact_id FROM civicrm_group_contact
346	WHERE civicrm_group_contact.group_id = {$values} AND civicrm_group_contact.status = 'Removed')";
347
348	//used only when exclude group is selected
349	if ($xGroups != 0) {
350	$smartSql .= " AND contact_a.id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
351	}
352
353	$smartGroupQuery = " INSERT IGNORE INTO
354	Ig_{$this->_tableName}(contact_id)
355	$smartSql";
356
357	CRM_Core_DAO::executeQuery($smartGroupQuery, CRM_Core_DAO::$_nullArray);
358	if ($this->_debug > 0) {
359	print "-- Smart group query: <pre>";
360	print "$smartGroupQuery;";
361	print "</pre>";
362	}
363	$insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
364	SET group_names = (SELECT title FROM civicrm_group
365	WHERE civicrm_group.id = $values)
366	WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
367	AND Ig_{$this->_tableName}.group_names IS NULL";
368	CRM_Core_DAO::executeQuery($insertGroupNameQuery, CRM_Core_DAO::$_nullArray);
369	if ($this->_debug > 0) {
370	print "-- Smart group query: <pre>";
371	print "$insertGroupNameQuery;";
372	print "</pre>";
373	}
374	}
375	}
376	}
377	// end if( $this->_groups ) condition
d14ccbdc	378	$this->buildACLClause('contact_a');
6a488035 TO	379	$from = "FROM civicrm_contact contact_a";
	380
	381	/* We need to join to this again to get the date_added value */
	382
d14ccbdc	383	$from .= " INNER JOIN dates_{$this->_tableName} d ON (contact_a.id = d.id) {$this->_aclFrom}";
6a488035 TO	384
	385	// Only include groups in the search query of one or more Include OR Exclude groups has been selected.
	386	// CRM-6356
	387	if ($this->_groups) {
	388	$from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)";
	389	}
	390
	391	return $from;
	392	}
	393
86538308 EM	394	/**
	395	* @param bool $includeContactIDs
	396	*
	397	* @return string
	398	*/
00be9182	399	public function where($includeContactIDs = FALSE) {
47b8444f SL	400	$where = '(1)';
47b8444f SL	401	if ($this->_aclWhere) {
b3798d48	402	$where .= " AND {$this->_aclWhere} ";
47b8444f SL	403	}
47b8444f SL	404	return $where;
6a488035 TO	405	}
6a488035 TO	406
86538308 EM	407	/**
	408	* @return string
	409	*/
00be9182	410	public function templateFile() {
6a488035 TO	411	return 'CRM/Contact/Form/Search/Custom.tpl';
	412	}
	413
86538308 EM	414	/**
	415	* @param $title
	416	*/
00be9182	417	public function setTitle($title) {
6a488035 TO	418	if ($title) {
	419	CRM_Utils_System::setTitle($title);
	420	}
	421	else {
	422	CRM_Utils_System::setTitle(ts('Search'));
	423	}
	424	}
	425
e0ef6999 EM	426	/**
	427	* @return mixed
	428	*/
00be9182	429	public function count() {
6a488035 TO	430	$sql = $this->all();
	431
	432	$dao = CRM_Core_DAO::executeQuery($sql,
	433	CRM_Core_DAO::$_nullArray
	434	);
	435	return $dao->N;
	436	}
	437
00be9182	438	public function __destruct() {
6a488035 TO	439	//drop the temp. tables if they exist
	440	if (!empty($this->_includeGroups)) {
	441	$sql = "DROP TEMPORARY TABLE IF EXISTS Ig_{$this->_tableName}";
	442	CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
	443	}
	444
	445	if (!empty($this->_excludeGroups)) {
	446	$sql = "DROP TEMPORARY TABLE IF EXISTS Xg_{$this->_tableName}";
	447	CRM_Core_DAO::executeQuery($sql, CRM_Core_DAO::$_nullArray);
	448	}
	449	}
96025800	450
d14ccbdc SL	451	/**
	452	* @param string $tableAlias
	453	*/
	454	public function buildACLClause($tableAlias = 'contact') {
	455	list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
	456	}
	457
6a488035	458	}