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

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

/**
 *
 * @package CRM
 * @copyright CiviCRM LLC (c) 2004-2013
 * $Id$
 *
 */
class CRM_Contact_Form_Search_Custom_Group
  extends CRM_Contact_Form_Search_Custom_Base
  implements CRM_Contact_Form_Search_Interface {

  protected $_formValues;

  protected $_tableName = NULL;

  protected $_where = ' (1) ';

  protected $_aclFrom = NULL;
  protected $_aclWhere = NULL;

  function __construct(&$formValues) {
    $this->_formValues = $formValues;
    $this->_columns = array(
      ts('Contact Id') => 'contact_id',
      ts('Contact Type') => 'contact_type',
      ts('Name') => 'sort_name',
      ts('Group Name') => 'gname',
      ts('Tag Name') => 'tname',
    );

    $this->_includeGroups = CRM_Utils_Array::value('includeGroups', $this->_formValues, array());
    $this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $this->_formValues, array());
    $this->_includeTags = CRM_Utils_Array::value('includeTags', $this->_formValues, array());
    $this->_excludeTags = CRM_Utils_Array::value('excludeTags', $this->_formValues, array());

    //define variables
    $this->_allSearch = FALSE;
    $this->_groups    = FALSE;
    $this->_tags      = FALSE;
    $this->_andOr     = CRM_Utils_Array::value('andOr', $this->_formValues);


    //make easy to check conditions for groups and tags are
    //selected or it is empty search
    if (empty($this->_includeGroups) && empty($this->_excludeGroups) &&
      empty($this->_includeTags) && empty($this->_excludeTags)
    ) {
      //empty search
      $this->_allSearch = TRUE;
    }

    $this->_groups = (!empty($this->_includeGroups) || !empty($this->_excludeGroups));

    $this->_tags = (!empty($this->_includeTags) || !empty($this->_excludeTags));
  }

  function __destruct() {
    // mysql drops the tables when connectiomn is terminated
    // cannot drop tables here, since the search might be used
    // in other parts after the object is destroyed
  }

  function buildForm(&$form) {

    $this->setTitle(ts('Include / Exclude Search'));

    $groups = CRM_Core_PseudoConstant::group();

    $tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
    if (count($groups) == 0 || count($tags) == 0) {
      CRM_Core_Session::setStatus(ts("At least one Group and Tag must be present for Custom Group / Tag search."), ts('Missing Group/Tag'));
      $url = CRM_Utils_System::url('civicrm/contact/search/custom/list', 'reset=1');
      CRM_Utils_System::redirect($url);
    }

    $inG = &$form->addElement('advmultiselect', 'includeGroups',
      ts('Include Group(s)') . ' ', $groups,
      array(
        'size' => 5,
        'style' => 'width:240px',
        'class' => 'advmultiselect',
      )
    );

    $outG = &$form->addElement('advmultiselect', 'excludeGroups',
      ts('Exclude Group(s)') . ' ', $groups,
      array(
        'size' => 5,
        'style' => 'width:240px',
        'class' => 'advmultiselect',
      )
    );

    $andOr = array(
      '1' => ts('Show contacts that meet the Groups criteria AND the Tags criteria'),
      '0' => ts('Show contacts that meet the Groups criteria OR  the Tags criteria'),
    );
    $form->addRadio('andOr', ts('AND/OR'), $andOr, NULL, '<br />', TRUE);

    $int = &$form->addElement('advmultiselect', 'includeTags',
      ts('Include Tag(s)') . ' ', $tags,
      array(
        'size' => 5,
        'style' => 'width:240px',
        'class' => 'advmultiselect',
      )
    );

    $outt = &$form->addElement('advmultiselect', 'excludeTags',
      ts('Exclude Tag(s)') . ' ', $tags,
      array(
        'size' => 5,
        'style' => 'width:240px',
        'class' => 'advmultiselect',
      )
    );

    //add/remove buttons for groups
    $inG->setButtonAttributes('add', array('value' => ts('Add >>')));;
    $outG->setButtonAttributes('add', array('value' => ts('Add >>')));;
    $inG->setButtonAttributes('remove', array('value' => ts('<< Remove')));;
    $outG->setButtonAttributes('remove', array('value' => ts('<< Remove')));;

    //add/remove buttons for tags
    $int->setButtonAttributes('add', array('value' => ts('Add >>')));;
    $outt->setButtonAttributes('add', array('value' => ts('Add >>')));;
    $int->setButtonAttributes('remove', array('value' => ts('<< Remove')));;
    $outt->setButtonAttributes('remove', array('value' => ts('<< Remove')));;

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

  /*
   * Set search form field defaults here.
   */
  function setDefaultValues() {
    $defaults = array( 'andOr' => '1' );

    if (!empty($this->_formValues)) {
      $defaults['andOr'] = CRM_Utils_Array::value('andOr', $this->_formValues, '1');

      $defaults['includeGroups'] = CRM_Utils_Array::value('includeGroups', $this->_formValues);
      $defaults['excludeGroups'] = CRM_Utils_Array::value('excludeGroups', $this->_formValues);
    }

    return $defaults;
  }

  function all(
    $offset = 0, $rowcount = 0, $sort = NULL,
    $includeContactIDs = FALSE, $justIDs = FALSE
  ) {

    if ($justIDs) {
      $selectClause = "contact_a.id as contact_id";
    }
    else {
      $selectClause = "contact_a.id as contact_id,
                         contact_a.contact_type as contact_type,
                         contact_a.sort_name    as sort_name";

      //distinguish column according to user selection
      if (($this->_includeGroups && !$this->_includeTags)) {
        unset($this->_columns['Tag Name']);
        $selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname";
      }
      elseif ($this->_includeTags && (!$this->_includeGroups)) {
        unset($this->_columns['Group Name']);
        $selectClause .= ", GROUP_CONCAT(DISTINCT tag_names  ORDER BY tag_names ASC ) as tname";
      }
      elseif (!empty($this->_includeTags) && !empty($this->_includeGroups)) {
        $selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname , GROUP_CONCAT(DISTINCT tag_names ORDER BY tag_names ASC ) as tname";
      }
      else {
        unset($this->_columns['Tag Name']);
        unset($this->_columns['Group Name']);
      }
    }

    $from = $this->from();

    $where = $this->where($includeContactIDs);

    if (!$justIDs && !$this->_allSearch) {
      $groupBy = " GROUP BY contact_a.id";
    }
    else {
      // CRM-10850
      // we do this since this if stmt is called by the smart group part of the code
      // adding a groupBy clause and saving it as a smart group messes up the query and
      // bad things happen
      // andrew hunt seemed to have rewritten this piece when he worked on this search
      $groupBy = null;
    }

    $sql = "SELECT $selectClause $from WHERE  $where $groupBy";

    // Define ORDER BY for query in $sort, with default value
    if (!$justIDs) {
      if (!empty($sort)) {
        if (is_string($sort)) {
          $sort = CRM_Utils_Type::escape($sort, 'String');
          $sql .= " ORDER BY $sort ";
        }
        else {
          $sql .= " ORDER BY " . trim($sort->orderBy());
        }
      }
      else {
        $sql .= " ORDER BY contact_id ASC";
      }
    }

    if ($offset >= 0 && $rowcount > 0) {
      $sql .= " LIMIT $offset, $rowcount ";
    }

    return $sql;
  }

  function from() {

    $iGroups = $xGroups = $iTags = $xTags = 0;

    //define table name
    $randomNum = md5(uniqid());
    $this->_tableName = "civicrm_temp_custom_{$randomNum}";

    //block for Group search
    $smartGroup = array();
    if ($this->_groups || $this->_allSearch) {
      $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 = NULL;
      }
      if (is_array($this->_excludeGroups)) {
        $xGroups = implode(',', $this->_excludeGroups);
      }
      else {
        $xGroups = 0;
      }

      $sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
      CRM_Core_DAO::executeQuery($sql);

      //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, civicrm_contact
                  WHERE
                     civicrm_contact.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);

        //search for smart group contacts
        foreach ($this->_excludeGroups as $keys => $values) {
          if (in_array($values, $smartGroup)) {
            $ssGroup = new CRM_Contact_DAO_Group();
            $ssGroup->id = $values;
            if (!$ssGroup->find(TRUE)) {
              CRM_Core_Error::fatal();
            }
            CRM_Contact_BAO_GroupContactCache::load($ssGroup);

            $smartSql = "
SELECT gcc.contact_id
FROM   civicrm_group_contact_cache gcc
WHERE  gcc.group_id = {$ssGroup->id}
";
            $smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
            CRM_Core_DAO::executeQuery($smartGroupQuery);
          }
        }
      }

      $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
                                                                   contact_id int,
                                                                   group_names varchar(64)) ENGINE=MyISAM";

      CRM_Core_DAO::executeQuery($sql);

      if ($iGroups) {
        $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
                 SELECT              civicrm_contact.id as contact_id, civicrm_group.title as group_name
                 FROM                civicrm_contact
                    INNER JOIN       civicrm_group_contact
                            ON       civicrm_group_contact.contact_id = civicrm_contact.id
                    LEFT JOIN        civicrm_group
                            ON       civicrm_group_contact.group_id = civicrm_group.id";
      }
      else {
        $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
                 SELECT              civicrm_contact.id as contact_id, ''
                 FROM                civicrm_contact";
      }


      //used only when exclude group is selected
      if ($xGroups != 0) {
        $includeGroup .= " LEFT JOIN        Xg_{$this->_tableName}
                                          ON       civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
      }

      if ($iGroups) {
        $includeGroup .= " WHERE
                                     civicrm_group_contact.status = 'Added'  AND
                                     civicrm_group_contact.group_id IN($iGroups)";
      }
      else {
        $includeGroup .= " WHERE ( 1 ) ";
      }

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

      CRM_Core_DAO::executeQuery($includeGroup);

      //search for smart group contacts

      foreach ($this->_includeGroups as $keys => $values) {
        if (in_array($values, $smartGroup)) {
          $ssGroup = new CRM_Contact_DAO_Group();
          $ssGroup->id = $values;
          if (!$ssGroup->find(TRUE)) {
            CRM_Core_Error::fatal();
          }
          CRM_Contact_BAO_GroupContactCache::load($ssGroup);

          $smartSql = "
SELECT gcc.contact_id
FROM   civicrm_group_contact_cache gcc
WHERE  gcc.group_id = {$ssGroup->id}
";

          //used only when exclude group is selected
          if ($xGroups != 0) {
            $smartSql .= " AND gcc.contact_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);
          $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);
        }
      }
    }
    //group contact search end here;

    //block for Tags search
    if ($this->_tags || $this->_allSearch) {
      //find all tags
      $tag = new CRM_Core_DAO_Tag();
      $tag->is_active = 1;
      $tag->find();
      while ($tag->fetch()) {
        $allTags[] = $tag->id;
      }
      $includedTags = implode(',', $allTags);

      if (!empty($this->_includeTags)) {
        $iTags = implode(',', $this->_includeTags);
      }
      else {
        //if no group selected search for all groups
        $iTags = NULL;
      }
      if (is_array($this->_excludeTags)) {
        $xTags = implode(',', $this->_excludeTags);
      }
      else {
        $xTags = 0;
      }

      $sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
      CRM_Core_DAO::executeQuery($sql);

      //used only when exclude tag is selected
      if ($xTags != 0) {
        $excludeTag = "INSERT INTO  Xt_{$this->_tableName} ( contact_id )
                  SELECT  DISTINCT civicrm_entity_tag.entity_id
                  FROM civicrm_entity_tag, civicrm_contact
                  WHERE
                     civicrm_entity_tag.entity_table = 'civicrm_contact' AND
                     civicrm_contact.id = civicrm_entity_tag.entity_id AND
                     civicrm_entity_tag.tag_id IN( {$xTags})";

        CRM_Core_DAO::executeQuery($excludeTag);
      }

      $sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
                                                               contact_id int,
                                                               tag_names varchar(64)) ENGINE=MyISAM";

      CRM_Core_DAO::executeQuery($sql);

      if ($iTags) {
        $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
                 SELECT              civicrm_contact.id as contact_id, civicrm_tag.name as tag_name
                 FROM                civicrm_contact
                    INNER JOIN       civicrm_entity_tag
                            ON       ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
                                       civicrm_entity_tag.entity_id = civicrm_contact.id )
                    LEFT JOIN        civicrm_tag
                            ON       civicrm_entity_tag.tag_id = civicrm_tag.id";
      }
      else {
        $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
                 SELECT              civicrm_contact.id as contact_id, ''
                 FROM                civicrm_contact";
      }

      //used only when exclude tag is selected
      if ($xTags != 0) {
        $includeTag .= " LEFT JOIN        Xt_{$this->_tableName}
                                       ON       civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
      }
      if ($iTags) {
        $includeTag .= " WHERE   civicrm_entity_tag.tag_id IN($iTags)";
      }
      else {
        $includeTag .= " WHERE ( 1 ) ";
      }

      //used only when exclude tag is selected
      if ($xTags != 0) {
        $includeTag .= " AND  Xt_{$this->_tableName}.contact_id IS null";
      }

      CRM_Core_DAO::executeQuery($includeTag);
    }

    $from = " FROM civicrm_contact contact_a";

    /*
     * CRM-10850 / CRM-10848
     * If we use include / exclude groups as smart groups for ACL's having the below causes
     * a cycle which messes things up. Hence commenting out for now
     * $this->buildACLClause('contact_a');
     */

    /*
         * check the situation and set booleans
         */

    $Ig = ($iGroups != 0);

    $It = ($iTags != 0);

    $Xg = ($xGroups != 0);

    $Xt = ($xTags != 0);

    //PICK UP FROM HERE
    if (!$this->_groups && !$this->_tags) {
      $this->_andOr = 1;
    }

    /*
         * Set from statement depending on array sel
         */
    $whereitems = array();
    foreach (array('Ig', 'It') as $inc) {
      if ($this->_andOr == 1) {
        if ($$inc) {
          $from .= " INNER JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
        }
      }
      else {
        if ($$inc) {
          $from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
        }
      }
      if ($$inc) {
        $whereitems[] = "temptable$inc.contact_id IS NOT NULL";
      }
    }
    $this->_where = $whereitems ? "(" . implode(' OR ', $whereitems) . ')' : '(1)';
    foreach (array('Xg', 'Xt') as $exc) {
      if ($$exc) {
        $from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable$exc ON (contact_a.id = temptable$exc.contact_id)";
        $this->_where .= " AND temptable$exc.contact_id IS NULL";
      }
    }

    $from .= " LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) ) {$this->_aclFrom}";

    if ($this->_aclWhere) {
      $this->_where .= " AND {$this->_aclWhere} ";
    }

    // also exclude all contacts that are deleted
    // CRM-11627
    $this->_where .= " AND (contact_a.is_deleted != 1) ";

    return $from;
  }

  function where($includeContactIDs = FALSE) {
    if ($includeContactIDs) {
      $contactIDs = array();

      foreach ($this->_formValues as $id => $value) {
        if ($value &&
          substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
        ) {
          $contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
        }
      }

      if (!empty($contactIDs)) {
        $contactIDs = implode(', ', $contactIDs);
        $clauses[] = "contact_a.id IN ( $contactIDs )";
      }
      $where = "{$this->_where} AND " . implode(' AND ', $clauses);
    }
    else {
      $where = $this->_where;
    }

    return $where;
  }

  /*
     * Functions below generally don't need to be modified
     */
  function count() {
    $sql = $this->all();

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

  function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
    return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
  }

  function &columns() {
    return $this->_columns;
  }

  function summary() {
    return NULL;
  }

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

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

  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	+--------------------------------------------------------------------+
232624b1	4	\| CiviCRM version 4.4 \|
6a488035 TO	5	+--------------------------------------------------------------------+
	6	\| Copyright CiviCRM LLC (c) 2004-2013 \|
	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
	31	* @copyright CiviCRM LLC (c) 2004-2013
	32	* $Id$
	33	*
	34	*/
ceefef9f DL	35	class CRM_Contact_Form_Search_Custom_Group
	36	extends CRM_Contact_Form_Search_Custom_Base
	37	implements CRM_Contact_Form_Search_Interface {
6a488035 TO	38
	39	protected $_formValues;
	40
	41	protected $_tableName = NULL;
	42
	43	protected $_where = ' (1) ';
	44
	45	protected $_aclFrom = NULL;
	46	protected $_aclWhere = NULL;
	47
	48	function __construct(&$formValues) {
	49	$this->_formValues = $formValues;
	50	$this->_columns = array(
	51	ts('Contact Id') => 'contact_id',
	52	ts('Contact Type') => 'contact_type',
	53	ts('Name') => 'sort_name',
	54	ts('Group Name') => 'gname',
	55	ts('Tag Name') => 'tname',
	56	);
	57
	58	$this->_includeGroups = CRM_Utils_Array::value('includeGroups', $this->_formValues, array());
	59	$this->_excludeGroups = CRM_Utils_Array::value('excludeGroups', $this->_formValues, array());
	60	$this->_includeTags = CRM_Utils_Array::value('includeTags', $this->_formValues, array());
	61	$this->_excludeTags = CRM_Utils_Array::value('excludeTags', $this->_formValues, array());
	62
	63	//define variables
	64	$this->_allSearch = FALSE;
	65	$this->_groups = FALSE;
	66	$this->_tags = FALSE;
	67	$this->_andOr = CRM_Utils_Array::value('andOr', $this->_formValues);
	68
	69
	70	//make easy to check conditions for groups and tags are
	71	//selected or it is empty search
	72	if (empty($this->_includeGroups) && empty($this->_excludeGroups) &&
	73	empty($this->_includeTags) && empty($this->_excludeTags)
	74	) {
	75	//empty search
	76	$this->_allSearch = TRUE;
	77	}
	78
	79	$this->_groups = (!empty($this->_includeGroups) \|\| !empty($this->_excludeGroups));
	80
	81	$this->_tags = (!empty($this->_includeTags) \|\| !empty($this->_excludeTags));
	82	}
	83
	84	function __destruct() {
	85	// mysql drops the tables when connectiomn is terminated
	86	// cannot drop tables here, since the search might be used
	87	// in other parts after the object is destroyed
	88	}
	89
	90	function buildForm(&$form) {
	91
	92	$this->setTitle(ts('Include / Exclude Search'));
	93
	94	$groups = CRM_Core_PseudoConstant::group();
	95
cd43c5e3	96	$tags = CRM_Core_PseudoConstant::get('CRM_Core_DAO_EntityTag', 'tag_id', array('onlyActive' => FALSE));
6a488035 TO	97	if (count($groups) == 0 \|\| count($tags) == 0) {
	98	CRM_Core_Session::setStatus(ts("At least one Group and Tag must be present for Custom Group / Tag search."), ts('Missing Group/Tag'));
	99	$url = CRM_Utils_System::url('civicrm/contact/search/custom/list', 'reset=1');
	100	CRM_Utils_System::redirect($url);
	101	}
	102
	103	$inG = &$form->addElement('advmultiselect', 'includeGroups',
	104	ts('Include Group(s)') . ' ', $groups,
	105	array(
	106	'size' => 5,
	107	'style' => 'width:240px',
	108	'class' => 'advmultiselect',
	109	)
	110	);
	111
	112	$outG = &$form->addElement('advmultiselect', 'excludeGroups',
	113	ts('Exclude Group(s)') . ' ', $groups,
	114	array(
	115	'size' => 5,
	116	'style' => 'width:240px',
	117	'class' => 'advmultiselect',
	118	)
	119	);
	120
	121	$andOr = array(
	122	'1' => ts('Show contacts that meet the Groups criteria AND the Tags criteria'),
	123	'0' => ts('Show contacts that meet the Groups criteria OR the Tags criteria'),
	124	);
29ba7662	125	$form->addRadio('andOr', ts('AND/OR'), $andOr, NULL, '<br />', TRUE);
6a488035 TO	126
	127	$int = &$form->addElement('advmultiselect', 'includeTags',
	128	ts('Include Tag(s)') . ' ', $tags,
	129	array(
	130	'size' => 5,
	131	'style' => 'width:240px',
	132	'class' => 'advmultiselect',
	133	)
	134	);
	135
	136	$outt = &$form->addElement('advmultiselect', 'excludeTags',
	137	ts('Exclude Tag(s)') . ' ', $tags,
	138	array(
	139	'size' => 5,
	140	'style' => 'width:240px',
	141	'class' => 'advmultiselect',
	142	)
	143	);
	144
	145	//add/remove buttons for groups
	146	$inG->setButtonAttributes('add', array('value' => ts('Add >>')));;
	147	$outG->setButtonAttributes('add', array('value' => ts('Add >>')));;
	148	$inG->setButtonAttributes('remove', array('value' => ts('<< Remove')));;
	149	$outG->setButtonAttributes('remove', array('value' => ts('<< Remove')));;
	150
	151	//add/remove buttons for tags
	152	$int->setButtonAttributes('add', array('value' => ts('Add >>')));;
	153	$outt->setButtonAttributes('add', array('value' => ts('Add >>')));;
	154	$int->setButtonAttributes('remove', array('value' => ts('<< Remove')));;
	155	$outt->setButtonAttributes('remove', array('value' => ts('<< Remove')));;
	156
	157	/**
	158	* if you are using the standard template, this array tells the template what elements
	159	* are part of the search criteria
	160	*/
	161	$form->assign('elements', array('includeGroups', 'excludeGroups', 'andOr', 'includeTags', 'excludeTags'));
	162	}
	163
	164	/*
	165	* Set search form field defaults here.
	166	*/
	167	function setDefaultValues() {
29ba7662 DL	168	$defaults = array( 'andOr' => '1' );
	169
	170	if (!empty($this->_formValues)) {
	171	$defaults['andOr'] = CRM_Utils_Array::value('andOr', $this->_formValues, '1');
	172
	173	$defaults['includeGroups'] = CRM_Utils_Array::value('includeGroups', $this->_formValues);
	174	$defaults['excludeGroups'] = CRM_Utils_Array::value('excludeGroups', $this->_formValues);
	175	}
	176
	177	return $defaults;
6a488035 TO	178	}
	179
	180	function all(
	181	$offset = 0, $rowcount = 0, $sort = NULL,
	182	$includeContactIDs = FALSE, $justIDs = FALSE
	183	) {
	184
	185	if ($justIDs) {
	186	$selectClause = "contact_a.id as contact_id";
	187	}
	188	else {
	189	$selectClause = "contact_a.id as contact_id,
	190	contact_a.contact_type as contact_type,
	191	contact_a.sort_name as sort_name";
	192
	193	//distinguish column according to user selection
	194	if (($this->_includeGroups && !$this->_includeTags)) {
	195	unset($this->_columns['Tag Name']);
	196	$selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname";
	197	}
	198	elseif ($this->_includeTags && (!$this->_includeGroups)) {
	199	unset($this->_columns['Group Name']);
	200	$selectClause .= ", GROUP_CONCAT(DISTINCT tag_names ORDER BY tag_names ASC ) as tname";
	201	}
	202	elseif (!empty($this->_includeTags) && !empty($this->_includeGroups)) {
	203	$selectClause .= ", GROUP_CONCAT(DISTINCT group_names ORDER BY group_names ASC ) as gname , GROUP_CONCAT(DISTINCT tag_names ORDER BY tag_names ASC ) as tname";
	204	}
	205	else {
	206	unset($this->_columns['Tag Name']);
	207	unset($this->_columns['Group Name']);
	208	}
	209	}
	210
	211	$from = $this->from();
	212
	213	$where = $this->where($includeContactIDs);
	214
	215	if (!$justIDs && !$this->_allSearch) {
	216	$groupBy = " GROUP BY contact_a.id";
	217	}
	218	else {
	219	// CRM-10850
	220	// we do this since this if stmt is called by the smart group part of the code
	221	// adding a groupBy clause and saving it as a smart group messes up the query and
	222	// bad things happen
	223	// andrew hunt seemed to have rewritten this piece when he worked on this search
	224	$groupBy = null;
	225	}
	226
	227	$sql = "SELECT $selectClause $from WHERE $where $groupBy";
	228
	229	// Define ORDER BY for query in $sort, with default value
	230	if (!$justIDs) {
	231	if (!empty($sort)) {
	232	if (is_string($sort)) {
21d32567	233	$sort = CRM_Utils_Type::escape($sort, 'String');
6a488035 TO	234	$sql .= " ORDER BY $sort ";
	235	}
	236	else {
	237	$sql .= " ORDER BY " . trim($sort->orderBy());
	238	}
	239	}
	240	else {
	241	$sql .= " ORDER BY contact_id ASC";
	242	}
	243	}
	244
	245	if ($offset >= 0 && $rowcount > 0) {
	246	$sql .= " LIMIT $offset, $rowcount ";
	247	}
	248
	249	return $sql;
	250	}
	251
	252	function from() {
	253
	254	$iGroups = $xGroups = $iTags = $xTags = 0;
	255
	256	//define table name
	257	$randomNum = md5(uniqid());
	258	$this->_tableName = "civicrm_temp_custom_{$randomNum}";
	259
	260	//block for Group search
	261	$smartGroup = array();
	262	if ($this->_groups \|\| $this->_allSearch) {
	263	$group = new CRM_Contact_DAO_Group();
	264	$group->is_active = 1;
	265	$group->find();
	266	while ($group->fetch()) {
	267	$allGroups[] = $group->id;
	268	if ($group->saved_search_id) {
	269	$smartGroup[$group->saved_search_id] = $group->id;
	270	}
	271	}
	272	$includedGroups = implode(',', $allGroups);
	273
	274	if (!empty($this->_includeGroups)) {
	275	$iGroups = implode(',', $this->_includeGroups);
	276	}
	277	else {
	278	//if no group selected search for all groups
	279	$iGroups = NULL;
	280	}
	281	if (is_array($this->_excludeGroups)) {
	282	$xGroups = implode(',', $this->_excludeGroups);
	283	}
	284	else {
	285	$xGroups = 0;
	286	}
	287
ceefef9f	288	$sql = "CREATE TEMPORARY TABLE Xg_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
6a488035 TO	289	CRM_Core_DAO::executeQuery($sql);
	290
	291	//used only when exclude group is selected
	292	if ($xGroups != 0) {
	293	$excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )
	294	SELECT DISTINCT civicrm_group_contact.contact_id
	295	FROM civicrm_group_contact, civicrm_contact
	296	WHERE
	297	civicrm_contact.id = civicrm_group_contact.contact_id AND
	298	civicrm_group_contact.status = 'Added' AND
	299	civicrm_group_contact.group_id IN( {$xGroups})";
	300
	301	CRM_Core_DAO::executeQuery($excludeGroup);
	302
	303	//search for smart group contacts
	304	foreach ($this->_excludeGroups as $keys => $values) {
	305	if (in_array($values, $smartGroup)) {
811a3ea4 DL	306	$ssGroup = new CRM_Contact_DAO_Group();
	307	$ssGroup->id = $values;
	308	if (!$ssGroup->find(TRUE)) {
	309	CRM_Core_Error::fatal();
	310	}
	311	CRM_Contact_BAO_GroupContactCache::load($ssGroup);
	312
	313	$smartSql = "
	314	SELECT gcc.contact_id
	315	FROM civicrm_group_contact_cache gcc
	316	WHERE gcc.group_id = {$ssGroup->id}
	317	";
6a488035	318	$smartGroupQuery = " INSERT IGNORE INTO Xg_{$this->_tableName}(contact_id) $smartSql";
6a488035 TO	319	CRM_Core_DAO::executeQuery($smartGroupQuery);
	320	}
	321	}
	322	}
	323
	324	$sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
	325	contact_id int,
ceefef9f	326	group_names varchar(64)) ENGINE=MyISAM";
6a488035 TO	327
	328	CRM_Core_DAO::executeQuery($sql);
	329
	330	if ($iGroups) {
	331	$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
	332	SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name
	333	FROM civicrm_contact
	334	INNER JOIN civicrm_group_contact
	335	ON civicrm_group_contact.contact_id = civicrm_contact.id
	336	LEFT JOIN civicrm_group
	337	ON civicrm_group_contact.group_id = civicrm_group.id";
	338	}
	339	else {
	340	$includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)
	341	SELECT civicrm_contact.id as contact_id, ''
	342	FROM civicrm_contact";
	343	}
	344
	345
	346	//used only when exclude group is selected
	347	if ($xGroups != 0) {
	348	$includeGroup .= " LEFT JOIN Xg_{$this->_tableName}
	349	ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id";
	350	}
	351
	352	if ($iGroups) {
	353	$includeGroup .= " WHERE
	354	civicrm_group_contact.status = 'Added' AND
	355	civicrm_group_contact.group_id IN($iGroups)";
	356	}
	357	else {
	358	$includeGroup .= " WHERE ( 1 ) ";
	359	}
	360
	361	//used only when exclude group is selected
	362	if ($xGroups != 0) {
	363	$includeGroup .= " AND Xg_{$this->_tableName}.contact_id IS null";
	364	}
	365
	366	CRM_Core_DAO::executeQuery($includeGroup);
	367
	368	//search for smart group contacts
	369
	370	foreach ($this->_includeGroups as $keys => $values) {
	371	if (in_array($values, $smartGroup)) {
811a3ea4 DL	372	$ssGroup = new CRM_Contact_DAO_Group();
	373	$ssGroup->id = $values;
	374	if (!$ssGroup->find(TRUE)) {
	375	CRM_Core_Error::fatal();
	376	}
	377	CRM_Contact_BAO_GroupContactCache::load($ssGroup);
6a488035	378
811a3ea4 DL	379	$smartSql = "
	380	SELECT gcc.contact_id
	381	FROM civicrm_group_contact_cache gcc
	382	WHERE gcc.group_id = {$ssGroup->id}
	383	";
6a488035 TO	384
	385	//used only when exclude group is selected
	386	if ($xGroups != 0) {
811a3ea4	387	$smartSql .= " AND gcc.contact_id NOT IN (SELECT contact_id FROM Xg_{$this->_tableName})";
6a488035 TO	388	}
	389
	390	$smartGroupQuery = " INSERT IGNORE INTO Ig_{$this->_tableName}(contact_id)
	391	$smartSql";
	392
	393	CRM_Core_DAO::executeQuery($smartGroupQuery);
	394	$insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}
	395	SET group_names = (SELECT title FROM civicrm_group
	396	WHERE civicrm_group.id = $values)
	397	WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL
	398	AND Ig_{$this->_tableName}.group_names IS NULL";
	399	CRM_Core_DAO::executeQuery($insertGroupNameQuery);
	400	}
	401	}
	402	}
	403	//group contact search end here;
	404
	405	//block for Tags search
	406	if ($this->_tags \|\| $this->_allSearch) {
	407	//find all tags
	408	$tag = new CRM_Core_DAO_Tag();
	409	$tag->is_active = 1;
	410	$tag->find();
	411	while ($tag->fetch()) {
	412	$allTags[] = $tag->id;
	413	}
	414	$includedTags = implode(',', $allTags);
	415
	416	if (!empty($this->_includeTags)) {
	417	$iTags = implode(',', $this->_includeTags);
	418	}
	419	else {
	420	//if no group selected search for all groups
	421	$iTags = NULL;
	422	}
	423	if (is_array($this->_excludeTags)) {
	424	$xTags = implode(',', $this->_excludeTags);
	425	}
	426	else {
	427	$xTags = 0;
	428	}
	429
ceefef9f	430	$sql = "CREATE TEMPORARY TABLE Xt_{$this->_tableName} ( contact_id int primary key) ENGINE=MyISAM";
6a488035 TO	431	CRM_Core_DAO::executeQuery($sql);
	432
	433	//used only when exclude tag is selected
	434	if ($xTags != 0) {
	435	$excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )
	436	SELECT DISTINCT civicrm_entity_tag.entity_id
	437	FROM civicrm_entity_tag, civicrm_contact
	438	WHERE
	439	civicrm_entity_tag.entity_table = 'civicrm_contact' AND
	440	civicrm_contact.id = civicrm_entity_tag.entity_id AND
	441	civicrm_entity_tag.tag_id IN( {$xTags})";
	442
	443	CRM_Core_DAO::executeQuery($excludeTag);
	444	}
	445
	446	$sql = "CREATE TEMPORARY TABLE It_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,
	447	contact_id int,
ceefef9f	448	tag_names varchar(64)) ENGINE=MyISAM";
6a488035 TO	449
	450	CRM_Core_DAO::executeQuery($sql);
	451
	452	if ($iTags) {
	453	$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
	454	SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name
	455	FROM civicrm_contact
	456	INNER JOIN civicrm_entity_tag
	457	ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND
	458	civicrm_entity_tag.entity_id = civicrm_contact.id )
	459	LEFT JOIN civicrm_tag
	460	ON civicrm_entity_tag.tag_id = civicrm_tag.id";
	461	}
	462	else {
	463	$includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)
	464	SELECT civicrm_contact.id as contact_id, ''
	465	FROM civicrm_contact";
	466	}
	467
	468	//used only when exclude tag is selected
	469	if ($xTags != 0) {
	470	$includeTag .= " LEFT JOIN Xt_{$this->_tableName}
	471	ON civicrm_contact.id = Xt_{$this->_tableName}.contact_id";
	472	}
	473	if ($iTags) {
	474	$includeTag .= " WHERE civicrm_entity_tag.tag_id IN($iTags)";
	475	}
	476	else {
	477	$includeTag .= " WHERE ( 1 ) ";
	478	}
	479
	480	//used only when exclude tag is selected
	481	if ($xTags != 0) {
	482	$includeTag .= " AND Xt_{$this->_tableName}.contact_id IS null";
	483	}
	484
	485	CRM_Core_DAO::executeQuery($includeTag);
	486	}
	487
	488	$from = " FROM civicrm_contact contact_a";
	489
	490	/*
	491	* CRM-10850 / CRM-10848
	492	* If we use include / exclude groups as smart groups for ACL's having the below causes
	493	* a cycle which messes things up. Hence commenting out for now
	494	* $this->buildACLClause('contact_a');
	495	*/
	496
	497	/*
	498	* check the situation and set booleans
	499	*/
	500
	501	$Ig = ($iGroups != 0);
	502
	503	$It = ($iTags != 0);
	504
	505	$Xg = ($xGroups != 0);
	506
	507	$Xt = ($xTags != 0);
	508
	509	//PICK UP FROM HERE
	510	if (!$this->_groups && !$this->_tags) {
	511	$this->_andOr = 1;
	512	}
513
514	/*
515	* Set from statement depending on array sel
516	*/
517	$whereitems = array();
518	foreach (array('Ig', 'It') as $inc) {
519	if ($this->_andOr == 1) {
520	if ($$inc) {
521	$from .= " INNER JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
522	}
523	}
524	else {
525	if ($$inc) {
526	$from .= " LEFT JOIN {$inc}_{$this->_tableName} temptable$inc ON (contact_a.id = temptable$inc.contact_id)";
527	}
528	}
529	if ($$inc) {
530	$whereitems[] = "temptable$inc.contact_id IS NOT NULL";
531	}
532	}
533	$this->_where = $whereitems ? "(" . implode(' OR ', $whereitems) . ')' : '(1)';
534	foreach (array('Xg', 'Xt') as $exc) {
535	if ($$exc) {
536	$from .= " LEFT JOIN {$exc}_{$this->_tableName} temptable$exc ON (contact_a.id = temptable$exc.contact_id)";
537	$this->_where .= " AND temptable$exc.contact_id IS NULL";
538	}
539	}
540
541	$from .= " LEFT JOIN civicrm_email ON ( contact_a.id = civicrm_email.contact_id AND ( civicrm_email.is_primary = 1 OR civicrm_email.is_bulkmail = 1 ) ) {$this->_aclFrom}";
542
543	if ($this->_aclWhere) {
544	$this->_where .= " AND {$this->_aclWhere} ";
545	}
546
547	// also exclude all contacts that are deleted
548	// CRM-11627
549	$this->_where .= " AND (contact_a.is_deleted != 1) ";
550
551	return $from;
552	}
553
554	function where($includeContactIDs = FALSE) {
555	if ($includeContactIDs) {
556	$contactIDs = array();
557
558	foreach ($this->_formValues as $id => $value) {
559	if ($value &&
560	substr($id, 0, CRM_Core_Form::CB_PREFIX_LEN) == CRM_Core_Form::CB_PREFIX
561	) {
562	$contactIDs[] = substr($id, CRM_Core_Form::CB_PREFIX_LEN);
563	}
564	}
565
566	if (!empty($contactIDs)) {
567	$contactIDs = implode(', ', $contactIDs);
568	$clauses[] = "contact_a.id IN ( $contactIDs )";
569	}
570	$where = "{$this->_where} AND " . implode(' AND ', $clauses);
571	}
572	else {
573	$where = $this->_where;
574	}
575
576	return $where;
577	}
578
579	/*
580	* Functions below generally don't need to be modified
581	*/
582	function count() {
583	$sql = $this->all();
584
585	$dao = CRM_Core_DAO::executeQuery($sql);
586	return $dao->N;
587	}
588
589	function contactIDs($offset = 0, $rowcount = 0, $sort = NULL, $returnSQL = FALSE) {
590	return $this->all($offset, $rowcount, $sort, FALSE, TRUE);
591	}
592
593	function &columns() {
594	return $this->_columns;
595	}
596
597	function summary() {
598	return NULL;
599	}
600
601	function templateFile() {
602	return 'CRM/Contact/Form/Search/Custom.tpl';
603	}
604
605	function setTitle($title) {
606	if ($title) {
607	CRM_Utils_System::setTitle($title);
608	}
609	else {
610	CRM_Utils_System::setTitle(ts('Search'));
611	}
612	}
613
614	function buildACLClause($tableAlias = 'contact') {
615	list($this->_aclFrom, $this->_aclWhere) = CRM_Contact_BAO_Contact_Permission::cacheClause($tableAlias);
616	}
617	}
618