PHP CRM_Contact_BAO_GroupContactCache::load方法代码示例

 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();
//.........这里部分代码省略.........

 function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a")
 {
     $config = CRM_Core_Config::singleton();
     // find all the groups that are part of a saved search
     $groupIDs = implode(',', $groups);
     if (empty($groupIDs)) {
         return NULL;
     }
     $sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHERE  id IN ( {$groupIDs} )\n  AND  ( saved_search_id != 0\n   OR    saved_search_id IS NOT NULL\n   OR    children IS NOT NULL )\n";
     $group = CRM_Core_DAO::executeQuery($sql);
     $ssWhere = array();
     while ($group->fetch()) {
         if ($tableAlias == NULL) {
             $alias = "`civicrm_group_contact_cache_{$group->id}`";
         } else {
             $alias = $tableAlias;
         }
         $this->_useDistinct = TRUE;
         if (!$this->_smartGroupCache || $group->cache_date == NULL) {
             CRM_Contact_BAO_GroupContactCache::load($group);
         }
         $this->_tables[$alias] = $this->_whereTables[$alias] = " LEFT JOIN civicrm_group_contact_cache {$alias} ON {$joinTable}.id = {$alias}.contact_id ";
         $ssWhere[] = "{$alias}.group_id = {$group->id}";
     }
     if (!empty($ssWhere)) {
         return implode(' OR ', $ssWhere);
     }
     return NULL;
 }

 /**
  * note that $job_id is used only as a variable in the temp table construction
  * and does not play a role in the queries generated
  * @param int $job_id
  *   (misnomer) a nonce value used to name temporary tables.
  * @param int $mailing_id
  * @param bool $storeRecipients
  * @param bool $dedupeEmail
  * @param null $mode
  *
  * @return CRM_Mailing_Event_BAO_Queue|string
  */
 public static function getRecipients($job_id, $mailing_id = NULL, $storeRecipients = FALSE, $dedupeEmail = FALSE, $mode = NULL)
 {
     $mailingGroup = new CRM_Mailing_DAO_MailingGroup();
     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
     $job = CRM_Mailing_BAO_MailingJob::getTableName();
     $mg = CRM_Mailing_DAO_MailingGroup::getTableName();
     $eq = CRM_Mailing_Event_DAO_Queue::getTableName();
     $email = CRM_Core_DAO_Email::getTableName();
     if ($mode == 'sms') {
         $phone = CRM_Core_DAO_Phone::getTableName();
     }
     $contact = CRM_Contact_DAO_Contact::getTableName();
     $group = CRM_Contact_DAO_Group::getTableName();
     $g2contact = CRM_Contact_DAO_GroupContact::getTableName();
     $m = new CRM_Mailing_DAO_Mailing();
     $m->id = $mailing_id;
     $m->find(TRUE);
     $email_selection_method = $m->email_selection_method;
     $location_type_id = $m->location_type_id;
     // Note: When determining the ORDER that results are returned, it's
     // the record that comes last that counts. That's because we are
     // INSERT'ing INTO a table with a primary id so that last record
     // over writes any previous record.
     switch ($email_selection_method) {
         case 'location-exclude':
             $location_filter = "({$email}.location_type_id != {$location_type_id})";
             // If there is more than one email that doesn't match the location,
             // prefer the one marked is_bulkmail, followed by is_primary.
             $order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary";
             break;
         case 'location-only':
             $location_filter = "({$email}.location_type_id = {$location_type_id})";
             // If there is more than one email of the desired location, prefer
             // the one marked is_bulkmail, followed by is_primary.
             $order_by = "ORDER BY {$email}.is_bulkmail, {$email}.is_primary";
             break;
         case 'location-prefer':
             $location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1 OR {$email}.location_type_id = {$location_type_id})";
             // ORDER BY is more complicated because we have to set an arbitrary
             // order that prefers the location that we want. We do that using
             // the FIELD function. For more info, see:
             // https://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_field
             // We assign the location type we want the value "1" by putting it
             // in the first position after we name the field. All other location
             // types are left out, so they will be assigned the value 0. That
             // means, they will all be equally tied for first place, with our
             // location being last.
             $order_by = "ORDER BY FIELD({$email}.location_type_id, {$location_type_id}), {$email}.is_bulkmail, {$email}.is_primary";
             break;
         case 'automatic':
             // fall through to default
         // fall through to default
         default:
             $location_filter = "({$email}.is_bulkmail = 1 OR {$email}.is_primary = 1)";
             $order_by = "ORDER BY {$email}.is_bulkmail";
     }
     /* Create a temp table for contact exclusion */
     $mailingGroup->query("CREATE TEMPORARY TABLE X_{$job_id}\n            (contact_id int primary key)\n            ENGINE=HEAP");
     /* Add all the members of groups excluded from this mailing to the temp
      * table */
     $excludeSubGroup = "INSERT INTO        X_{$job_id} (contact_id)\n                    SELECT  DISTINCT    {$g2contact}.contact_id\n                    FROM                {$g2contact}\n                    INNER JOIN          {$mg}\n                            ON          {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n                    WHERE\n                                        {$mg}.mailing_id = {$mailing_id}\n                        AND             {$g2contact}.status = 'Added'\n                        AND             {$mg}.group_type = 'Exclude'";
     $mailingGroup->query($excludeSubGroup);
     /* Add all unsubscribe members of base group from this mailing to the temp
      * table */
     $unSubscribeBaseGroup = "INSERT INTO        X_{$job_id} (contact_id)\n                    SELECT  DISTINCT    {$g2contact}.contact_id\n                    FROM                {$g2contact}\n                    INNER JOIN          {$mg}\n                            ON          {$g2contact}.group_id = {$mg}.entity_id AND {$mg}.entity_table = '{$group}'\n                    WHERE\n                                        {$mg}.mailing_id = {$mailing_id}\n                        AND             {$g2contact}.status = 'Removed'\n                        AND             {$mg}.group_type = 'Base'";
     $mailingGroup->query($unSubscribeBaseGroup);
     /* Add all the (intended) recipients of an excluded prior mailing to
      * the temp table */
     $excludeSubMailing = "INSERT IGNORE INTO X_{$job_id} (contact_id)\n                    SELECT  DISTINCT    {$eq}.contact_id\n                    FROM                {$eq}\n                    INNER JOIN          {$job}\n                            ON          {$eq}.job_id = {$job}.id\n                    INNER JOIN          {$mg}\n                            ON          {$job}.mailing_id = {$mg}.entity_id AND {$mg}.entity_table = '{$mailing}'\n                    WHERE\n                                        {$mg}.mailing_id = {$mailing_id}\n                        AND             {$mg}.group_type = 'Exclude'";
     $mailingGroup->query($excludeSubMailing);
     // get all the saved searches AND hierarchical groups
     // and load them in the cache
     $sql = "\nSELECT     {$group}.id, {$group}.cache_date, {$group}.saved_search_id, {$group}.children\nFROM       {$group}\nINNER JOIN {$mg} ON {$mg}.entity_id = {$group}.id\nWHERE      {$mg}.entity_table = '{$group}'\n  AND      {$mg}.group_type = 'Exclude'\n  AND      {$mg}.mailing_id = {$mailing_id}\n  AND      ( saved_search_id != 0\n   OR        saved_search_id IS NOT NULL\n   OR        children IS NOT NULL )\n";
     $groupDAO = CRM_Core_DAO::executeQuery($sql);
     while ($groupDAO->fetch()) {
         if ($groupDAO->cache_date == NULL) {
             CRM_Contact_BAO_GroupContactCache::load($groupDAO);
         }
         $smartGroupExclude = "\nINSERT IGNORE INTO X_{$job_id} (contact_id)\nSELECT c.contact_id\nFROM   civicrm_group_contact_cache c\nWHERE  c.group_id = {$groupDAO->id}\n";
         $mailingGroup->query($smartGroupExclude);
     }
     $tempColumn = 'email_id';
     if ($mode == 'sms') {
         $tempColumn = 'phone_id';
     }
     /* Get all the group contacts we want to include */
     $mailingGroup->query("CREATE TEMPORARY TABLE I_{$job_id}\n            ({$tempColumn} int, contact_id int primary key)\n            ENGINE=HEAP");
     /* Get the group contacts, but only those which are not in the
//.........这里部分代码省略.........

 public static function whereClause($type, &$tables, &$whereTables, $contactID = NULL)
 {
     $acls = CRM_ACL_BAO_Cache::build($contactID);
     //CRM_Core_Error::debug( "a: $contactID", $acls );
     $whereClause = NULL;
     $clauses = array();
     if (!empty($acls)) {
         $aclKeys = array_keys($acls);
         $aclKeys = implode(',', $aclKeys);
         $query = "\nSELECT   a.operation, a.object_id\n  FROM   civicrm_acl_cache c, civicrm_acl a\n WHERE   c.acl_id       =  a.id\n   AND   a.is_active    =  1\n   AND   a.object_table = 'civicrm_saved_search'\n   AND   a.id        IN ( {$aclKeys} )\nORDER BY a.object_id\n";
         $dao = CRM_Core_DAO::executeQuery($query);
         // do an or of all the where clauses u see
         $ids = array();
         while ($dao->fetch()) {
             // make sure operation matches the type TODO
             if (self::matchType($type, $dao->operation)) {
                 if (!$dao->object_id) {
                     $ids = array();
                     $whereClause = ' ( 1 ) ';
                     break;
                 }
                 $ids[] = $dao->object_id;
             }
         }
         if (!empty($ids)) {
             $ids = implode(',', $ids);
             $query = "\nSELECT g.*\n  FROM civicrm_group g\n WHERE g.id IN ( {$ids} )\n AND   g.is_active = 1\n";
             $dao = CRM_Core_DAO::executeQuery($query);
             $staticGroupIDs = array();
             $cachedGroupIDs = array();
             while ($dao->fetch()) {
                 // currently operation is restrcited to VIEW/EDIT
                 if ($dao->where_clause) {
                     if ($dao->select_tables) {
                         $tmpTables = array();
                         foreach (unserialize($dao->select_tables) as $tmpName => $tmpInfo) {
                             if ($tmpName == '`civicrm_group_contact-' . $dao->id . '`') {
                                 $tmpName = '`civicrm_group_contact-ACL`';
                                 $tmpInfo = str_replace('civicrm_group_contact-' . $dao->id, 'civicrm_group_contact-ACL', $tmpInfo);
                             } elseif ($tmpName == '`civicrm_group_contact_cache_' . $dao->id . '`') {
                                 $tmpName = '`civicrm_group_contact_cache-ACL`';
                                 $tmpInfo = str_replace('civicrm_group_contact_cache_' . $dao->id, 'civicrm_group_contact_cache-ACL', $tmpInfo);
                             }
                             $tmpTables[$tmpName] = $tmpInfo;
                         }
                         $tables = array_merge($tables, $tmpTables);
                     }
                     if ($dao->where_tables) {
                         $tmpTables = array();
                         foreach (unserialize($dao->where_tables) as $tmpName => $tmpInfo) {
                             if ($tmpName == '`civicrm_group_contact-' . $dao->id . '`') {
                                 $tmpName = '`civicrm_group_contact-ACL`';
                                 $tmpInfo = str_replace('civicrm_group_contact-' . $dao->id, 'civicrm_group_contact-ACL', $tmpInfo);
                                 $staticGroupIDs[] = $dao->id;
                             } elseif ($tmpName == '`civicrm_group_contact_cache_' . $dao->id . '`') {
                                 $tmpName = '`civicrm_group_contact_cache-ACL`';
                                 $tmpInfo = str_replace('civicrm_group_contact_cache_' . $dao->id, 'civicrm_group_contact_cache-ACL', $tmpInfo);
                                 $cachedGroupIDs[] = $dao->id;
                             }
                             $tmpTables[$tmpName] = $tmpInfo;
                         }
                         $whereTables = array_merge($whereTables, $tmpTables);
                     }
                 }
                 if (($dao->saved_search_id || $dao->children || $dao->parents) && $dao->cache_date == NULL) {
                     CRM_Contact_BAO_GroupContactCache::load($dao);
                 }
             }
             if ($staticGroupIDs) {
                 $clauses[] = '( `civicrm_group_contact-ACL`.group_id IN (' . join(', ', $staticGroupIDs) . ') AND `civicrm_group_contact-ACL`.status IN ("Added") )';
             }
             if ($cachedGroupIDs) {
                 $clauses[] = '`civicrm_group_contact_cache-ACL`.group_id IN (' . join(', ', $cachedGroupIDs) . ')';
             }
         }
     }
     if (!empty($clauses)) {
         $whereClause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
     }
     // call the hook to get additional whereClauses
     CRM_Utils_Hook::aclWhereClause($type, $tables, $whereTables, $contactID, $whereClause);
     if (empty($whereClause)) {
         $whereClause = ' ( 0 ) ';
     }
     return $whereClause;
 }

 /**
  * Allow removing contact from a parent group even if contact is in
  * a child group. (CRM-8858)
  */
 function testRemoveFromParentSmartGroup()
 {
     // Create smart group $parent
     $params = array('name' => 'Deceased Contacts', 'title' => 'Deceased Contacts', 'is_active' => 1, 'formValues' => array('is_deceased' => 1));
     $parent = CRM_Contact_BAO_Group::createSmartGroup($params);
     $this->registerTestObjects(array($parent));
     // Create group $child in $parent
     $params = array('name' => 'Child Group', 'title' => 'Child Group', 'is_active' => 1, 'parents' => array($parent->id => 1));
     $child = CRM_Contact_BAO_Group::create($params);
     $this->registerTestObjects(array($child));
     // Create $c1, $c2, $c3
     $deceased = $this->createTestObject('CRM_Contact_DAO_Contact', array('is_deceased' => 1), 3);
     // Add $c1, $c2, $c3 to $child
     foreach ($deceased as $contact) {
         $result = $this->callAPISuccess('group_contact', 'create', array('contact_id' => $contact->id, 'group_id' => $child->id));
     }
     // GroupContactCache::load()
     CRM_Contact_BAO_GroupContactCache::load($parent, TRUE);
     $this->assertCacheMatches(array($deceased[0]->id, $deceased[1]->id, $deceased[2]->id), $parent->id);
     // Remove $c1 from $parent
     $result = civicrm_api('group_contact', 'create', array('contact_id' => $deceased[0]->id, 'group_id' => $parent->id, 'status' => 'Removed', 'version' => '3'));
     $this->assertAPISuccess($result);
     // Assert $c1 not in $parent
     CRM_Contact_BAO_GroupContactCache::load($parent, TRUE);
     $this->assertCacheMatches(array($deceased[1]->id, $deceased[2]->id), $parent->id);
     // Assert $c1 still in $child
     $this->assertDBQuery(1, 'select count(*) from civicrm_group_contact where group_id=%1 and contact_id=%2 and status=%3', array(1 => array($child->id, 'Integer'), 2 => array($deceased[0]->id, 'Integer'), 3 => array('Added', 'String')));
 }

 /**
  * @param array $groups
  * @param string $tableAlias
  * @param string $joinTable
  * @param string $op
  *
  * @return null|string
  */
 public function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a", $op)
 {
     $isNullOp = strpos($op, 'NULL') !== FALSE;
     $groupsIds = $groups;
     if (!$isNullOp && !$groups) {
         return NULL;
     } elseif (strpos($op, 'IN') !== FALSE) {
         $groups = array($op => $groups);
     } elseif (is_array($groups) && count($groups)) {
         $groups = array('IN' => $groups);
     }
     // Find all the groups that are part of a saved search.
     $smartGroupClause = self::buildClause("id", $op, $groups, 'Int');
     $sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHERE  {$smartGroupClause}\n  AND  ( saved_search_id != 0\n   OR    saved_search_id IS NOT NULL\n   OR    children IS NOT NULL )\n";
     $group = CRM_Core_DAO::executeQuery($sql);
     while ($group->fetch()) {
         $this->_useDistinct = TRUE;
         if (!$this->_smartGroupCache || $group->cache_date == NULL) {
             CRM_Contact_BAO_GroupContactCache::load($group);
         }
     }
     if (!$tableAlias) {
         $tableAlias = "`civicrm_group_contact_cache_";
         $tableAlias .= $isNullOp ? "a`" : implode(',', (array) $groupsIds) . "`";
     }
     $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache {$tableAlias} ON {$joinTable}.id = {$tableAlias}.contact_id ";
     return self::buildClause("{$tableAlias}.group_id", $op, $groups, 'Int');
 }

 /**
  * Set up a smart group testing scenario.
  *
  * @return array
  */
 protected function setupSmartGroup()
 {
     $params = array('name' => 'Deceased Contacts', 'title' => 'Deceased Contacts', 'is_active' => 1, 'formValues' => array('is_deceased' => 1));
     $group = CRM_Contact_BAO_Group::createSmartGroup($params);
     $this->registerTestObjects(array($group));
     // Create contacts $y1, $y2, $y3 which do match $g; create $n1, $n2, $n3 which do not match $g
     $living = $this->createTestObject('CRM_Contact_DAO_Contact', array('is_deceased' => 0), 3);
     $deceased = $this->createTestObject('CRM_Contact_DAO_Contact', array('is_deceased' => 1), 3);
     $this->assertEquals(3, count($deceased));
     $this->assertEquals(3, count($living));
     // Assert: $g cache has exactly $y1, $y2, $y3
     CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
     $group->find(TRUE);
     $this->assertCacheMatches(array($deceased[0]->id, $deceased[1]->id, $deceased[2]->id), $group->id);
     // Reload the group so we have the cache_date & refresh_date.
     return array($group, $living, $deceased);
 }

 /**
  * Check we can load smart groups based on config from 'real DBs' without fatal errors.
  *
  * Note that we are only testing lack of errors at this stage
  * @todo - for some reason the data was getting truncated from the group table using dataprovider - would be preferable to get that working
  * //@notdataProvider dataProviderSavedSearch
  * //@notparam integer $groupID
  *
  * To add to this dataset do
  *
  *  SET @groupID = x;
  *  SELECT mapping_id FROM civicrm_group g LEFT JOIN civicrm_saved_search s ON saved_search_id = s.id WHERE g.id = @groupID INTO @mappingID;
  * SELECT * FROM civicrm_mapping WHERE id = @mappingID;
  * SELECT * FROM civicrm_mapping_field WHERE mapping_id = @mappingID;
  * SELECT * FROM civicrm_saved_search WHERE mapping_id = @mappingID;
  * SELECT g.* FROM civicrm_saved_search s LEFT JOIN civicrm_group g ON g.saved_search_id =  s.id WHERE  mapping_id = @mappingID;
  *
  *  Copy the output to a single sql file and place in the SavedSearchDataSets folder - use the group number as the prefix.
  *  Try to keep as much of the real world irregular glory as you can! Don't change the table ids to be number 1 as this can hide errors
  */
 public function testGroupData()
 {
     $groups = $this->dataProviderSavedSearch();
     foreach ($groups[0] as $groupID) {
         $group = new CRM_Contact_BAO_Group();
         $group->id = $groupID;
         $group->find(TRUE);
         CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
     }
 }

 /**
  * @param array  $groups
  * @param string $tableAlias
  * @param string $joinTable
  *
  * @return null|string
  */
 function addGroupContactCache($groups, $tableAlias = NULL, $joinTable = "contact_a")
 {
     $config = CRM_Core_Config::singleton();
     // Find all the groups that are part of a saved search.
     $groupIDs = implode(',', $groups);
     if (empty($groupIDs)) {
         return NULL;
     }
     $sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHERE  id IN ( {$groupIDs} )\n  AND  ( saved_search_id != 0\n   OR    saved_search_id IS NOT NULL\n   OR    children IS NOT NULL )\n";
     $group = CRM_Core_DAO::executeQuery($sql);
     $groupsFiltered = array();
     while ($group->fetch()) {
         $groupsFiltered[] = $group->id;
         $this->_useDistinct = TRUE;
         if (!$this->_smartGroupCache || $group->cache_date == NULL) {
             CRM_Contact_BAO_GroupContactCache::load($group);
         }
     }
     if (count($groupsFiltered)) {
         $groupIDsFiltered = implode(',', $groupsFiltered);
         if ($tableAlias == NULL) {
             $tableAlias = "civicrm_group_contact_cache_{$groupIDsFiltered}";
         }
         $this->_tables[$tableAlias] = $this->_whereTables[$tableAlias] = " LEFT JOIN civicrm_group_contact_cache `{$tableAlias}` ON {$joinTable}.id = `{$tableAlias}`.contact_id ";
         return "`{$tableAlias}`.group_id IN (" . $groupIDsFiltered . ")";
     }
     return NULL;
 }

 /**
  * where / qill clause for smart groups
  *
  * @return void
  * @access public
  */
 function savedSearch(&$values)
 {
     list($name, $op, $value, $grouping, $wildcard) = $values;
     $config = CRM_Core_Config::singleton();
     // find all the groups that are part of a saved search
     $groupIDs = implode(',', array_keys($value));
     $sql = "\nSELECT id, cache_date, saved_search_id, children\nFROM   civicrm_group\nWHERE  id IN ( {$groupIDs} )\n  AND  ( saved_search_id != 0\n   OR    saved_search_id IS NOT NULL\n   OR    children IS NOT NULL )\n";
     $group = CRM_Core_DAO::executeQuery($sql);
     $ssWhere = array();
     while ($group->fetch()) {
         $this->_useDistinct = true;
         if (!$this->_smartGroupCache || $group->cache_date == null) {
             require_once 'CRM/Contact/BAO/GroupContactCache.php';
             CRM_Contact_BAO_GroupContactCache::load($group);
         }
         $gcTable = "`civicrm_group_contact_cache_{$group->id}`";
         $this->_tables[$gcTable] = $this->_whereTables[$gcTable] = " LEFT JOIN civicrm_group_contact_cache {$gcTable} ON contact_a.id = {$gcTable}.contact_id ";
         $ssWhere[] = "{$gcTable}.group_id = {$group->id}";
     }
     if (!empty($ssWhere)) {
         return implode(' OR ', $ssWhere);
     }
     return null;
 }

 /**
  * Test smart groups with non-numeric don't fail on range queries.
  *
  * CRM-14720
  */
 public function testNumericPostal()
 {
     $this->individualCreate(array('api.address.create' => array('postal_code' => 5, 'location_type_id' => 'Main')));
     $this->individualCreate(array('api.address.create' => array('postal_code' => 'EH10 4RB-889', 'location_type_id' => 'Main')));
     $this->individualCreate(array('api.address.create' => array('postal_code' => '4', 'location_type_id' => 'Main')));
     $this->individualCreate(array('api.address.create' => array('postal_code' => '6', 'location_type_id' => 'Main')));
     $params = array(array('postal_code_low', '=', 5, 0, 0));
     CRM_Contact_BAO_Query::convertFormValues($params);
     $query = new CRM_Contact_BAO_Query($params, array('contact_id'), NULL, TRUE, FALSE, 1, TRUE, TRUE, FALSE);
     $sql = $query->query(FALSE);
     $result = CRM_Core_DAO::executeQuery(implode(' ', $sql));
     $this->assertEquals(2, $result->N);
     // We save this as a smart group and then load it. With mysql warnings on & CRM-14720 this
     // results in mysql warnings & hence fatal errors.
     /// I was unable to get mysql warnings to activate in the context of the unit tests - but
     // felt this code still provided a useful bit of coverage as it runs the various queries to load
     // the group & could generate invalid sql if a bug were introduced.
     $groupParams = array('title' => 'postal codes', 'formValues' => $params, 'is_active' => 1);
     $group = CRM_Contact_BAO_Group::createSmartGroup($groupParams);
     CRM_Contact_BAO_GroupContactCache::load($group, TRUE);
 }

 public static function whereClause($type, &$tables, &$whereTables, $contactID = null)
 {
     require_once 'CRM/ACL/BAO/Cache.php';
     $acls =& CRM_ACL_BAO_Cache::build($contactID);
     //CRM_Core_Error::debug( "a: $contactID", $acls );
     $whereClause = null;
     $clauses = array();
     if (!empty($acls)) {
         $aclKeys = array_keys($acls);
         $aclKeys = implode(',', $aclKeys);
         $query = "\nSELECT   a.operation, a.object_id\n  FROM   civicrm_acl_cache c, civicrm_acl a\n WHERE   c.acl_id       =  a.id\n   AND   a.is_active    =  1\n   AND   a.object_table = 'civicrm_saved_search'\n   AND   a.id        IN ( {$aclKeys} )\nORDER BY a.object_id\n";
         $dao =& CRM_Core_DAO::executeQuery($query);
         // do an or of all the where clauses u see
         $ids = array();
         while ($dao->fetch()) {
             // make sure operation matches the type TODO
             if (self::matchType($type, $dao->operation)) {
                 if (!$dao->object_id) {
                     $ids = array();
                     $whereClause = ' ( 1 ) ';
                     break;
                 }
                 $ids[] = $dao->object_id;
             }
         }
         if (!empty($ids)) {
             $ids = implode(',', $ids);
             $query = "\nSELECT g.*\n  FROM civicrm_group g\n WHERE g.id IN ( {$ids} )\n";
             $dao =& CRM_Core_DAO::executeQuery($query);
             while ($dao->fetch()) {
                 // currently operation is restrcited to VIEW/EDIT
                 if ($dao->where_clause) {
                     $clauses[] = $dao->where_clause;
                     if ($dao->select_tables) {
                         $tables = array_merge($tables, unserialize($dao->select_tables));
                     }
                     if ($dao->where_tables) {
                         $whereTables = array_merge($whereTables, unserialize($dao->where_tables));
                     }
                 }
                 if (($dao->saved_search_id || $dao->children || $dao->parents) && $dao->cache_date == null) {
                     require_once 'CRM/Contact/BAO/GroupContactCache.php';
                     CRM_Contact_BAO_GroupContactCache::load($dao);
                 }
             }
         }
     }
     if (!empty($clauses)) {
         $whereClause = ' ( ' . implode(' OR ', $clauses) . ' ) ';
     }
     // call the hook to get additional whereClauses
     require_once 'CRM/Utils/Hook.php';
     CRM_Utils_Hook::aclWhereClause($type, $tables, $whereTables, $contactID, $whereClause);
     if (empty($whereClause)) {
         $whereClause = ' ( 0 ) ';
     }
     return $whereClause;
 }