[civicrm-core.git] / CRM / Contact / Form / Search / Custom / DateAdded.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 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-2014
 * $Id$
 *
 */
class CRM_Contact_Form_Search_Custom_DateAdded extends CRM_Contact_Form_Search_Custom_Base implements CRM_Contact_Form_Search_Interface {

  protected $_debug = 0;

  /**
   * @param $formValues
   */
  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
   */
  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
   */
  function summary() {
    return NULL;
  }

  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
   */
  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
   */
  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

    $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)";

    // 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
   */
  function where($includeContactIDs = FALSE) {
    return '(1)';
  }

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

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

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

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

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