function from() { //define table name $randomNum = md5(uniqid()); $this->_tableName = "civicrm_temp_custom_{$randomNum}"; //block for Group search $smartGroup = array(); if ($this->_groups || $this->_allSearch) { require_once 'CRM/Contact/DAO/Group.php'; $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=HEAP"; CRM_Core_DAO::executeQuery($sql); //used only when exclude group is selected if ($xGroups != 0) { $excludeGroup = "INSERT INTO Xg_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_group_contact.contact_id\n FROM civicrm_group_contact, civicrm_contact \n WHERE \n civicrm_contact.id = civicrm_group_contact.contact_id AND \n civicrm_group_contact.status = 'Added' AND\n 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)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql = $smartSql . " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact \n 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); } } } $sql = "CREATE TEMPORARY TABLE Ig_{$this->_tableName} ( id int PRIMARY KEY AUTO_INCREMENT,\n contact_id int,\n group_names varchar(64)) ENGINE=HEAP"; CRM_Core_DAO::executeQuery($sql); if ($iGroups) { $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name\n FROM civicrm_contact\n INNER JOIN civicrm_group_contact\n ON civicrm_group_contact.contact_id = civicrm_contact.id\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id"; } else { $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact"; } //used only when exclude group is selected if ($xGroups != 0) { $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id"; } if ($iGroups) { $includeGroup .= " WHERE \n civicrm_group_contact.status = 'Added' AND\n 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)) { $ssId = CRM_Utils_Array::key($values, $smartGroup); $smartSql = CRM_Contact_BAO_SavedSearch::contactIDsSQL($ssId); $smartSql .= " AND contact_a.id NOT IN ( \n SELECT contact_id FROM civicrm_group_contact\n 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) \n {$smartSql}"; CRM_Core_DAO::executeQuery($smartGroupQuery); $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}\n SET group_names = (SELECT title FROM civicrm_group\n WHERE civicrm_group.id = {$values})\n WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL \n 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 require_once 'CRM/Core/DAO/Tag.php'; $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=HEAP"; CRM_Core_DAO::executeQuery($sql); //used only when exclude tag is selected if ($xTags != 0) { $excludeTag = "INSERT INTO Xt_{$this->_tableName} ( contact_id )\n SELECT DISTINCT civicrm_entity_tag.entity_id\n FROM civicrm_entity_tag, civicrm_contact \n WHERE \n civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_contact.id = civicrm_entity_tag.entity_id AND \n 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,\n contact_id int,\n tag_names varchar(64)) ENGINE=HEAP"; CRM_Core_DAO::executeQuery($sql); if ($iTags) { $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name\n FROM civicrm_contact\n INNER JOIN civicrm_entity_tag\n ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = civicrm_contact.id )\n LEFT JOIN civicrm_tag\n ON civicrm_entity_tag.tag_id = civicrm_tag.id"; } else { $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact"; } //used only when exclude tag is selected if ($xTags != 0) { $includeTag .= " LEFT JOIN Xt_{$this->_tableName}\n 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"; /* * check the situation and set booleans */ if ($iGroups != 0) { $iG = true; } else { $iG = false; } if ($iTags != 0) { $iT = true; } else { $iT = false; } if ($xGroups != 0) { $xG = true; } else { $xG = false; } if ($xTags != 0) { $xT = true; } else { $xT = false; } if (!$this->_groups || !$this->_tags) { $this->_andOr = 1; } /* * Set from statement depending on array sel */ if ($iG && $iT && $xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable4 ON (contact_a.id = temptable4.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL OR temptable4.contact_id IS NOT NULL)"; } } if ($iG && $iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if ($iG && $iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if ($iG && $iT && !$xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " INNER JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL )"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if ($iG && !$iT && $xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName}) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if ($iG && !$iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if ($iG && !$iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if ($iG && !$iT && !$xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } } if (!$iG && $iT && $xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName}) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable3 ON (contact_a.id = temptable3.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL OR\n temptable3.contact_id IS NOT NULL)"; } } if (!$iG && $iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && $iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL) AND contact_a.id NOT IN(\n SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && $iT && !$xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } else { $from .= " LEFT JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL)"; } } if (!$iG && !$iT && $xG && $xT) { if ($this->_andOr == 1) { $this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})\n AND contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Xg_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xt_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && !$iT && !$xG && $xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN It_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xt_{$this->_tableName})"; } else { $from .= " LEFT JOIN Xt_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN It_{$this->_tableName} temptable2 ON (contact_a.id = temptable2.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT NULL OR temptable2.contact_id IS NOT NULL)"; } } if (!$iG && !$iT && $xG && !$xT) { if ($this->_andOr == 1) { $from .= " INNER JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "contact_a.id NOT IN(SELECT contact_id FROM Xg_{$this->_tableName})"; } else { $from .= " LEFT JOIN Ig_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $from .= " LEFT JOIN Xg_{$this->_tableName} temptable1 ON (contact_a.id = temptable1.contact_id)"; $this->_where = "( temptable1.contact_id IS NOT 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 ) )"; return $from; }
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 )\n SELECT DISTINCT civicrm_group_contact.contact_id\n FROM civicrm_group_contact, civicrm_contact\n WHERE\n civicrm_contact.id = civicrm_group_contact.contact_id AND\n civicrm_group_contact.status = 'Added' AND\n 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 = "\nSELECT gcc.contact_id\nFROM civicrm_group_contact_cache gcc\nWHERE gcc.group_id = {$ssGroup->id}\n"; $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,\n contact_id int,\n group_names varchar(64)) ENGINE=MyISAM"; CRM_Core_DAO::executeQuery($sql); if ($iGroups) { $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, civicrm_group.title as group_name\n FROM civicrm_contact\n INNER JOIN civicrm_group_contact\n ON civicrm_group_contact.contact_id = civicrm_contact.id\n LEFT JOIN civicrm_group\n ON civicrm_group_contact.group_id = civicrm_group.id"; } else { $includeGroup = "INSERT INTO Ig_{$this->_tableName} (contact_id, group_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact"; } //used only when exclude group is selected if ($xGroups != 0) { $includeGroup .= " LEFT JOIN Xg_{$this->_tableName}\n ON civicrm_contact.id = Xg_{$this->_tableName}.contact_id"; } if ($iGroups) { $includeGroup .= " WHERE\n civicrm_group_contact.status = 'Added' AND\n 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 = "\nSELECT gcc.contact_id\nFROM civicrm_group_contact_cache gcc\nWHERE gcc.group_id = {$ssGroup->id}\n"; //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)\n {$smartSql}"; CRM_Core_DAO::executeQuery($smartGroupQuery); $insertGroupNameQuery = "UPDATE IGNORE Ig_{$this->_tableName}\n SET group_names = (SELECT title FROM civicrm_group\n WHERE civicrm_group.id = {$values})\n WHERE Ig_{$this->_tableName}.contact_id IS NOT NULL\n 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 )\n SELECT DISTINCT civicrm_entity_tag.entity_id\n FROM civicrm_entity_tag, civicrm_contact\n WHERE\n civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_contact.id = civicrm_entity_tag.entity_id AND\n 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,\n contact_id int,\n tag_names varchar(64)) ENGINE=MyISAM"; CRM_Core_DAO::executeQuery($sql); if ($iTags) { $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, civicrm_tag.name as tag_name\n FROM civicrm_contact\n INNER JOIN civicrm_entity_tag\n ON ( civicrm_entity_tag.entity_table = 'civicrm_contact' AND\n civicrm_entity_tag.entity_id = civicrm_contact.id )\n LEFT JOIN civicrm_tag\n ON civicrm_entity_tag.tag_id = civicrm_tag.id"; } else { $includeTag = "INSERT INTO It_{$this->_tableName} (contact_id, tag_names)\n SELECT civicrm_contact.id as contact_id, ''\n FROM civicrm_contact"; } //used only when exclude tag is selected if ($xTags != 0) { $includeTag .= " LEFT JOIN Xt_{$this->_tableName}\n 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; }
/** * @param array $usedFor * @param bool $buildSelect * @param bool $all * @param int $parentId * * @return array */ public static function getTagsUsedFor($usedFor = array('civicrm_contact'), $buildSelect = TRUE, $all = FALSE, $parentId = NULL) { $tags = array(); if (empty($usedFor)) { return $tags; } if (!is_array($usedFor)) { $usedFor = array($usedFor); } if ($parentId === NULL) { $parentClause = " parent_id IS NULL AND "; } else { $parentClause = " parent_id = {$parentId} AND "; } foreach ($usedFor as $entityTable) { $tag = new CRM_Core_DAO_Tag(); $tag->fields(); $tag->orderBy('parent_id'); if ($buildSelect) { $tag->whereAdd("is_tagset = 0 AND {$parentClause} used_for LIKE '%{$entityTable}%'"); } else { $tag->whereAdd("used_for LIKE '%{$entityTable}%'"); } if (!$all) { $tag->is_tagset = 0; } $tag->find(); while ($tag->fetch()) { if ($buildSelect) { $tags[$tag->id] = $tag->name; } else { $tags[$tag->id]['name'] = $tag->name; $tags[$tag->id]['parent_id'] = $tag->parent_id; $tags[$tag->id]['is_tagset'] = $tag->is_tagset; $tags[$tag->id]['used_for'] = $tag->used_for; } } $tag->free(); } return $tags; }