/**
  * Unsubscribe a contact from all groups that received this mailing.
  *
  * @param int $job_id
  *   The job ID.
  * @param int $queue_id
  *   The Queue Event ID of the recipient.
  * @param string $hash
  *   The hash.
  * @param bool $return
  *   If true return the list of groups.
  *
  * @return array|null
  *   $groups    Array of all groups from which the contact was removed, or null if the queue event could not be found.
  */
 public static function &unsub_from_mailing($job_id, $queue_id, $hash, $return = FALSE)
 {
     // First make sure there's a matching queue event.
     $q = CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
     $success = NULL;
     if (!$q) {
         return $success;
     }
     $contact_id = $q->contact_id;
     $transaction = new CRM_Core_Transaction();
     $do = new CRM_Core_DAO();
     $mgObject = new CRM_Mailing_DAO_MailingGroup();
     $mg = $mgObject->getTableName();
     $jobObject = new CRM_Mailing_BAO_MailingJob();
     $job = $jobObject->getTableName();
     $mailingObject = new CRM_Mailing_BAO_Mailing();
     $mailing = $mailingObject->getTableName();
     $groupObject = new CRM_Contact_BAO_Group();
     $group = $groupObject->getTableName();
     $gcObject = new CRM_Contact_BAO_GroupContact();
     $gc = $gcObject->getTableName();
     //We Need the mailing Id for the hook...
     $do->query("SELECT {$job}.mailing_id as mailing_id\n                     FROM   {$job}\n                     WHERE {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer'));
     $do->fetch();
     $mailing_id = $do->mailing_id;
     $do->query("\n            SELECT      {$mg}.entity_table as entity_table,\n                        {$mg}.entity_id as entity_id,\n                        {$mg}.group_type as group_type\n            FROM        {$mg}\n            INNER JOIN  {$job}\n                ON      {$job}.mailing_id = {$mg}.mailing_id\n            INNER JOIN  {$group}\n                ON      {$mg}.entity_id = {$group}.id\n            WHERE       {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer') . "\n                AND     {$mg}.group_type IN ('Include', 'Base')\n                AND     {$group}.is_hidden = 0");
     // Make a list of groups and a list of prior mailings that received
     // this mailing.
     $groups = array();
     $base_groups = array();
     $mailings = array();
     while ($do->fetch()) {
         if ($do->entity_table == $group) {
             if ($do->group_type == 'Base') {
                 $base_groups[$do->entity_id] = NULL;
             } else {
                 $groups[$do->entity_id] = NULL;
             }
         } elseif ($do->entity_table == $mailing) {
             $mailings[] = $do->entity_id;
         }
     }
     // As long as we have prior mailings, find their groups and add to the
     // list.
     while (!empty($mailings)) {
         $do->query("\n                SELECT      {$mg}.entity_table as entity_table,\n                            {$mg}.entity_id as entity_id\n                FROM        {$mg}\n                WHERE       {$mg}.mailing_id IN (" . implode(', ', $mailings) . ")\n                    AND     {$mg}.group_type = 'Include'");
         $mailings = array();
         while ($do->fetch()) {
             if ($do->entity_table == $group) {
                 $groups[$do->entity_id] = TRUE;
             } elseif ($do->entity_table == $mailing) {
                 $mailings[] = $do->entity_id;
             }
         }
     }
     //Pass the groups to be unsubscribed from through a hook.
     $group_ids = array_keys($groups);
     $base_group_ids = array_keys($base_groups);
     CRM_Utils_Hook::unsubscribeGroups('unsubscribe', $mailing_id, $contact_id, $group_ids, $base_group_ids);
     // Now we have a complete list of recipient groups.  Filter out all
     // those except smart groups, those that the contact belongs to and
     // base groups from search based mailings.
     $baseGroupClause = '';
     if (!empty($base_group_ids)) {
         $baseGroupClause = "OR  {$group}.id IN(" . implode(', ', $base_group_ids) . ")";
     }
     $groupIdClause = '';
     if ($group_ids || $base_group_ids) {
         $groupIdClause = "AND {$group}.id IN (" . implode(', ', array_merge($group_ids, $base_group_ids)) . ")";
     }
     $do->query("\n            SELECT      {$group}.id as group_id,\n                        {$group}.title as title,\n                        {$group}.description as description\n            FROM        {$group}\n            LEFT JOIN   {$gc}\n                ON      {$gc}.group_id = {$group}.id\n            WHERE       {$group}.is_hidden = 0\n                        {$groupIdClause}\n                AND     ({$group}.saved_search_id is not null\n                            OR  ({$gc}.contact_id = {$contact_id}\n                                AND {$gc}.status = 'Added')\n                            {$baseGroupClause}\n                        )");
     if ($return) {
         $returnGroups = array();
         while ($do->fetch()) {
             $returnGroups[$do->group_id] = array('title' => $do->title, 'description' => $do->description);
         }
         return $returnGroups;
     } else {
         while ($do->fetch()) {
             $groups[$do->group_id] = $do->title;
         }
     }
     $contacts = array($contact_id);
     foreach ($groups as $group_id => $group_name) {
         $notremoved = FALSE;
         if ($group_name) {
             if (in_array($group_id, $base_group_ids)) {
                 list($total, $removed, $notremoved) = CRM_Contact_BAO_GroupContact::addContactsToGroup($contacts, $group_id, 'Email', 'Removed');
             } else {
                 list($total, $removed, $notremoved) = CRM_Contact_BAO_GroupContact::removeContactsFromGroup($contacts, $group_id, 'Email');
             }
         }
         if ($notremoved) {
             unset($groups[$group_id]);
         }
     }
     $ue = new CRM_Mailing_Event_BAO_Unsubscribe();
     $ue->event_queue_id = $queue_id;
     $ue->org_unsubscribe = 0;
     $ue->time_stamp = date('YmdHis');
     $ue->save();
     $transaction->commit();
     return $groups;
 }
 /**
  * Unsubscribe a contact from all groups that received this mailing
  *
  * @param int $job_id       The job ID
  * @param int $queue_id     The Queue Event ID of the recipient
  * @param string $hash      The hash
  * @param boolean $return   If true return the list of groups.
  * @return array|null $groups    Array of all groups from which the contact was removed, or null if the queue event could not be found.
  * @access public
  * @static
  */
 public static function &unsub_from_mailing($job_id, $queue_id, $hash, $return = false)
 {
     /* First make sure there's a matching queue event */
     $q =& CRM_Mailing_Event_BAO_Queue::verify($job_id, $queue_id, $hash);
     $success = null;
     if (!$q) {
         return $success;
     }
     $contact_id = $q->contact_id;
     require_once 'CRM/Core/Transaction.php';
     $transaction = new CRM_Core_Transaction();
     $do = new CRM_Core_DAO();
     $mg = CRM_Mailing_DAO_Group::getTableName();
     $job = CRM_Mailing_BAO_Job::getTableName();
     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
     $group = CRM_Contact_BAO_Group::getTableName();
     $gc = CRM_Contact_BAO_GroupContact::getTableName();
     $do->query("\n            SELECT      {$mg}.entity_table as entity_table,\n                        {$mg}.entity_id as entity_id\n            FROM        {$mg}\n            INNER JOIN  {$job}\n                ON      {$job}.mailing_id = {$mg}.mailing_id\n            WHERE       {$job}.id = " . CRM_Utils_Type::escape($job_id, 'Integer') . "\n                AND     {$mg}.group_type = 'Include'");
     /* Make a list of groups and a list of prior mailings that received 
      * this mailing */
     $groups = array();
     $mailings = array();
     while ($do->fetch()) {
         if ($do->entity_table == $group) {
             //$groups[$do->entity_id] = true;
             $groups[$do->entity_id] = null;
         } else {
             if ($do->entity_table == $mailing) {
                 $mailings[] = $do->entity_id;
             }
         }
     }
     /* As long as we have prior mailings, find their groups and add to the
      * list */
     while (!empty($mailings)) {
         $do->query("\n                SELECT      {$mg}.entity_table as entity_table,\n                            {$mg}.entity_id as entity_id\n                FROM        {$mg}\n                WHERE       {$mg}.mailing_id IN (" . implode(', ', $mailings) . ")\n                    AND     {$mg}.group_type = 'Include'");
         $mailings = array();
         while ($do->fetch()) {
             if ($do->entity_table == $group) {
                 $groups[$do->entity_id] = true;
             } else {
                 if ($do->entity_table == $mailing) {
                     $mailings[] = $do->entity_id;
                 }
             }
         }
     }
     /* Now we have a complete list of recipient groups.  Filter out all
      * those except smart groups and those that the contact belongs to */
     $do->query("\n            SELECT      {$group}.id as group_id,\n                        {$group}.title as title,\n                        {$group}.description as description\n            FROM        {$group}\n            LEFT JOIN   {$gc}\n                ON      {$gc}.group_id = {$group}.id\n            WHERE       {$group}.id IN (" . implode(', ', array_keys($groups)) . ")\n                AND     ({$group}.saved_search_id is not null\n                            OR  ({$gc}.contact_id = {$contact_id}\n                                AND {$gc}.status = 'Added')\n                        )");
     if ($return) {
         while ($do->fetch()) {
             $groups[$do->group_id] = array('title' => $do->title, 'description' => $do->description);
         }
         return $groups;
     } else {
         while ($do->fetch()) {
             $groups[$do->group_id] = $do->title;
         }
     }
     $contacts = array($contact_id);
     foreach ($groups as $group_id => $group_name) {
         $notremoved = false;
         if ($group_name) {
             list($total, $removed, $notremoved) = CRM_Contact_BAO_GroupContact::removeContactsFromGroup($contacts, $group_id, 'Email');
         }
         if ($notremoved) {
             unset($groups[$group_id]);
         }
     }
     $ue = new CRM_Mailing_Event_BAO_Unsubscribe();
     $ue->event_queue_id = $queue_id;
     $ue->org_unsubscribe = 0;
     $ue->time_stamp = date('YmdHis');
     $ue->save();
     $transaction->commit();
     return $groups;
 }