CRM/Mailing/Event/BAO/Opened.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 /**
  13  *
  14  * @package CRM
  15  * @copyright CiviCRM LLC https://civicrm.org/licensing
  16  */
  17 class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened {
  18
  19   /**
  20    * Register an open event.
  21    *
  22    * @param int $queue_id
  23    *   The Queue Event ID of the recipient.
  24    *
  25    * @return bool
  26    */
  27   public static function open($queue_id) {
  28     // First make sure there's a matching queue event.
  29
  30     $success = FALSE;
  31
  32     $q = new CRM_Mailing_Event_BAO_Queue();
  33     $q->id = $queue_id;
  34     if ($q->find(TRUE)) {
  35       $oe = new CRM_Mailing_Event_BAO_Opened();
  36       $oe->event_queue_id = $queue_id;
  37       $oe->time_stamp = date('YmdHis');
  38       $oe->save();
  39       $success = TRUE;
  40     }
  41
  42     return $success;
  43   }
  44
  45   /**
  46    * Get row count for the event selector.
  47    *
  48    * @param int $mailing_id
  49    *   ID of the mailing.
  50    * @param int $job_id
  51    *   Optional ID of a job to filter on.
  52    * @param bool $is_distinct
  53    *   Group by queue ID?.
  54    *
  55    * @param string $toDate
  56    *
  57    * @return int
  58    *   Number of rows in result set
  59    */
  60   public static function getTotalCount(
  61     $mailing_id,
  62     $job_id = NULL,
  63     $is_distinct = FALSE,
  64     $toDate = NULL
  65   ) {
  66     $dao = new CRM_Core_DAO();
  67
  68     $open = self::getTableName();
  69     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
  70     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
  71     $job = CRM_Mailing_BAO_MailingJob::getTableName();
  72
  73     $query = "
  74             SELECT      COUNT($open.id) as opened
  75             FROM        $open
  76             INNER JOIN  $queue
  77                     ON  $open.event_queue_id = $queue.id
  78             INNER JOIN  $job
  79                     ON  $queue.job_id = $job.id
  80             INNER JOIN  $mailing
  81                     ON  $job.mailing_id = $mailing.id
  82                     AND $job.is_test = 0
  83             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
  84
  85     if (!empty($toDate)) {
  86       $query .= " AND $open.time_stamp <= $toDate";
  87     }
  88
  89     if (!empty($job_id)) {
  90       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
  91     }
  92
  93     if ($is_distinct) {
  94       $query .= " GROUP BY $queue.id ";
  95     }
  96
  97     $dao->query($query);
  98     $dao->fetch();
  99     if ($is_distinct) {
 100       return $dao->N;
 101     }
 102     else {
 103       return $dao->opened ? $dao->opened : 0;
 104     }
 105   }
 106
 107   /**
 108    * @see https://issues.civicrm.org/jira/browse/CRM-12814
 109    * Get opened count for each mailing for a given set of mailing IDs
 110    *
 111    * @param $mailingIDs
 112    *
 113    * @return array
 114    *   Opened count per mailing ID
 115    */
 116   public static function getMailingTotalCount($mailingIDs) {
 117     $dao = new CRM_Core_DAO();
 118     $openedCount = [];
 119
 120     $open = self::getTableName();
 121     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 122     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 123     $mailingIDs = implode(',', $mailingIDs);
 124
 125     $query = "
 126       SELECT $job.mailing_id as mailingID, COUNT($open.id) as opened
 127       FROM $open
 128       INNER JOIN $queue
 129         ON  $open.event_queue_id = $queue.id
 130       INNER JOIN $job
 131         ON  $queue.job_id = $job.id
 132         AND $job.is_test = 0
 133       WHERE $job.mailing_id IN ({$mailingIDs})
 134       GROUP BY civicrm_mailing_job.mailing_id
 135     ";
 136
 137     $dao->query($query);
 138
 139     while ($dao->fetch()) {
 140       $openedCount[$dao->mailingID] = $dao->opened;
 141     }
 142     return $openedCount;
 143   }
 144
 145   /**
 146    * Get opened count for each mailing for a given set of mailing IDs and a specific contact.
 147    *
 148    * @param int[] $mailingIDs
 149    *   IDs of the mailing (comma separated).
 150    * @param int $contactID
 151    *   ID of the contact.
 152    *
 153    * @return array
 154    *   Count per mailing ID
 155    */
 156   public static function getMailingContactCount($mailingIDs, $contactID) {
 157     $dao = new CRM_Core_DAO();
 158     $openedCount = [];
 159
 160     $open = self::getTableName();
 161     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 162     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 163     $mailingIDs = implode(',', $mailingIDs);
 164
 165     $query = "
 166       SELECT $job.mailing_id as mailingID, COUNT($open.id) as opened
 167       FROM $open
 168       INNER JOIN $queue
 169         ON  $open.event_queue_id = $queue.id
 170         AND $queue.contact_id = $contactID
 171       INNER JOIN $job
 172         ON  $queue.job_id = $job.id
 173         AND $job.is_test = 0
 174       WHERE $job.mailing_id IN ({$mailingIDs})
 175       GROUP BY civicrm_mailing_job.mailing_id
 176     ";
 177
 178     $dao->query($query);
 179
 180     while ($dao->fetch()) {
 181       $openedCount[$dao->mailingID] = $dao->opened;
 182     }
 183
 184     return $openedCount;
 185   }
 186
 187   /**
 188    * Get rows for the event browser.
 189    *
 190    * @param int $mailing_id
 191    *   ID of the mailing.
 192    * @param int $job_id
 193    *   Optional ID of the job.
 194    * @param bool $is_distinct
 195    *   Group by queue id?.
 196    * @param int $offset
 197    *   Offset.
 198    * @param int $rowCount
 199    *   Number of rows.
 200    * @param array $sort
 201    *   Sort array.
 202    *
 203    * @param int $contact_id
 204    *
 205    * @return array
 206    *   Result set
 207    */
 208   public static function &getRows(
 209     $mailing_id, $job_id = NULL,
 210     $is_distinct = FALSE, $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
 211   ) {
 212     $dao = new CRM_Core_DAO();
 213
 214     $open = self::getTableName();
 215     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 216     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 217     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 218     $contact = CRM_Contact_BAO_Contact::getTableName();
 219     $email = CRM_Core_BAO_Email::getTableName();
 220
 221     $selectClauses = [
 222       "$contact.display_name as display_name",
 223       "$contact.id as contact_id",
 224       "$email.email as email",
 225       ($is_distinct) ? "MIN({$open}.time_stamp) as date" : "{$open}.time_stamp as date",
 226     ];
 227
 228     if ($is_distinct) {
 229       $groupBy = " GROUP BY $queue.id ";
 230       $select = CRM_Contact_BAO_Query::appendAnyValueToSelect($selectClauses, "$queue.id");
 231     }
 232     else {
 233       $groupBy = '';
 234       $select = " SELECT " . implode(', ', $selectClauses);
 235     }
 236
 237     $query = "
 238             $select
 239             FROM        $contact
 240             INNER JOIN  $queue
 241                     ON  $queue.contact_id = $contact.id
 242             INNER JOIN  $email
 243                     ON  $queue.email_id = $email.id
 244             INNER JOIN  $open
 245                     ON  $open.event_queue_id = $queue.id
 246             INNER JOIN  $job
 247                     ON  $queue.job_id = $job.id
 248             INNER JOIN  $mailing
 249                     ON  $job.mailing_id = $mailing.id
 250                     AND $job.is_test = 0
 251             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 252
 253     if (!empty($job_id)) {
 254       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 255     }
 256
 257     if (!empty($contact_id)) {
 258       $query .= " AND $contact.id = " . CRM_Utils_Type::escape($contact_id, 'Integer');
 259     }
 260
 261     $query .= $groupBy;
 262
 263     $orderBy = "sort_name ASC";
 264     if (!$is_distinct) {
 265       $orderBy .= ", {$open}.time_stamp DESC";
 266     }
 267     if ($sort) {
 268       if (is_string($sort)) {
 269         $sort = CRM_Utils_Type::escape($sort, 'String');
 270         $orderBy = $sort;
 271       }
 272       else {
 273         $orderBy = trim($sort->orderBy());
 274       }
 275     }
 276
 277     $query .= " ORDER BY {$orderBy} ";
 278
 279     if ($offset || $rowCount) {
 280       //Added "||$rowCount" to avoid displaying all records on first page
 281       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 282     }
 283
 284     $dao->query($query);
 285
 286     $results = [];
 287
 288     while ($dao->fetch()) {
 289       $url = CRM_Utils_System::url('civicrm/contact/view',
 290         "reset=1&cid={$dao->contact_id}"
 291       );
 292       $results[] = [
 293         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 294         'email' => $dao->email,
 295         'date' => CRM_Utils_Date::customFormat($dao->date),
 296       ];
 297     }
 298     return $results;
 299   }
 300
 301 }