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