/** * Get the list of groups for contact based on status of group membership. * * @param int $contactId * Contact id. * @param string $status * State of membership. * @param int $numGroupContact * Number of groups for a contact that should be shown. * @param bool $count * True if we are interested only in the count. * @param bool $ignorePermission * True if we should ignore permissions for the current user. * useful in profile where permissions are limited for the user. If left * at false only groups viewable by the current user are returned * @param bool $onlyPublicGroups * True if we want to hide system groups. * * @param bool $excludeHidden * * @return array (reference)|int $values * the relevant data object values for the contact or * the total count when $count is TRUE */ public static function &getContactGroup($contactId, $status = NULL, $numGroupContact = NULL, $count = FALSE, $ignorePermission = FALSE, $onlyPublicGroups = FALSE, $excludeHidden = TRUE, $groupId = NULL) { if ($count) { $select = 'SELECT count(DISTINCT civicrm_group_contact.id)'; } else { $select = 'SELECT civicrm_group_contact.id as civicrm_group_contact_id, civicrm_group.title as group_title, civicrm_group.visibility as visibility, civicrm_group_contact.status as status, civicrm_group.id as group_id, civicrm_group.is_hidden as is_hidden, civicrm_subscription_history.date as date, civicrm_subscription_history.method as method'; } $where = " WHERE contact_a.id = %1 AND civicrm_group.is_active = 1"; if ($excludeHidden) { $where .= " AND civicrm_group.is_hidden = 0 "; } $params = array(1 => array($contactId, 'Integer')); if (!empty($status)) { $where .= ' AND civicrm_group_contact.status = %2'; $params[2] = array($status, 'String'); } if (!empty($groupId)) { $where .= " AND civicrm_group.id = %3 "; $params[3] = array($groupId, 'Integer'); } $tables = array('civicrm_group_contact' => 1, 'civicrm_group' => 1, 'civicrm_subscription_history' => 1); $whereTables = array(); if ($ignorePermission) { $permission = ' ( 1 ) '; } else { $permission = CRM_Core_Permission::getPermissionedStaticGroupClause(CRM_Core_Permission::VIEW, $tables, $whereTables); } $from = CRM_Contact_BAO_Query::fromClause($tables); //CRM-16945: seems hackish but as per CRM-16483 of using group criteria for Search Builder it is mandatory //to include group_contact_cache clause when group table is present, so following code remove duplicacy $from = str_replace("OR civicrm_group.id = civicrm_group_contact_cache.group_id", 'AND civicrm_group.saved_search_id IS NULL', $from); $where .= " AND {$permission} "; if ($onlyPublicGroups) { $where .= " AND civicrm_group.visibility != 'User and User Admin Only' "; } $order = $limit = ''; if (!$count) { $order = ' ORDER BY civicrm_group.title, civicrm_subscription_history.date ASC'; if ($numGroupContact) { $limit = " LIMIT 0, {$numGroupContact}"; } } $sql = $select . $from . $where . $order . $limit; if ($count) { $result = CRM_Core_DAO::singleValueQuery($sql, $params); return $result; } else { $dao = CRM_Core_DAO::executeQuery($sql, $params); $values = array(); while ($dao->fetch()) { $id = $dao->civicrm_group_contact_id; $values[$id]['id'] = $id; $values[$id]['group_id'] = $dao->group_id; $values[$id]['title'] = $dao->group_title; $values[$id]['visibility'] = $dao->visibility; $values[$id]['is_hidden'] = $dao->is_hidden; switch ($dao->status) { case 'Added': $prefix = 'in_'; break; case 'Removed': $prefix = 'out_'; break; default: $prefix = 'pending_'; } $values[$id][$prefix . 'date'] = $dao->date; $values[$id][$prefix . 'method'] = $dao->method; if ($status == 'Removed') { $query = "SELECT `date` as `date_added` FROM civicrm_subscription_history WHERE id = (SELECT max(id) FROM civicrm_subscription_history WHERE contact_id = %1 AND status = \"Added\" AND group_id = {$dao->group_id} )"; $dateDAO = CRM_Core_DAO::executeQuery($query, $params); if ($dateDAO->fetch()) { $values[$id]['date_added'] = $dateDAO->date_added; } } } return $values; } }