CRM/Mailing/Event/BAO/TrackableURLOpen.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_TrackableURLOpen extends CRM_Mailing_Event_DAO_TrackableURLOpen {
  18
  19   /**
  20    * Track a click-through and return the URL to redirect.
  21    *
  22    * If the numbers don't match up, return the base url.
  23    *
  24    * @param int $queue_id
  25    *   The Queue Event ID of the clicker.
  26    * @param int $url_id
  27    *   The ID of the trackable URL.
  28    *
  29    * @return string
  30    *   The redirection url, or base url on failure.
  31    */
  32   public static function track($queue_id, $url_id) {
  33     // To find the url, we also join on the queue and job tables.  This
  34     // prevents foreign key violations.
  35     $job = CRM_Utils_Type::escape(CRM_Mailing_BAO_MailingJob::getTableName(), 'MysqlColumnNameOrAlias');
  36     $eq = CRM_Utils_Type::escape(CRM_Mailing_Event_BAO_Queue::getTableName(), 'MysqlColumnNameOrAlias');
  37     $turl = CRM_Utils_Type::escape(CRM_Mailing_BAO_TrackableURL::getTableName(), 'MysqlColumnNameOrAlias');
  38
  39     if (!$queue_id) {
  40       $search = CRM_Core_DAO::executeQuery(
  41         "SELECT url
  42            FROM $turl
  43           WHERE $turl.id = %1",
  44         [
  45           1 => [$url_id, 'Integer'],
  46         ]
  47       );
  48
  49       if (!$search->fetch()) {
  50         return CRM_Utils_System::baseURL();
  51       }
  52
  53       return $search->url;
  54     }
  55
  56     $search = CRM_Core_DAO::executeQuery(
  57       "SELECT $turl.url as url
  58          FROM $turl
  59         INNER JOIN $job ON $turl.mailing_id = $job.mailing_id
  60         INNER JOIN $eq ON $job.id = $eq.job_id
  61         WHERE $eq.id = %1 AND $turl.id = %2",
  62       [
  63         1 => [$queue_id, 'Integer'],
  64         2 => [$url_id, 'Integer'],
  65       ]
  66     );
  67
  68     if (!$search->fetch()) {
  69       // Can't find either the URL or the queue. If we can find the URL then
  70       // return the URL without tracking.  Otherwise return the base URL.
  71       $search = CRM_Core_DAO::executeQuery(
  72         "SELECT $turl.url as url
  73            FROM $turl
  74           WHERE $turl.id = %1",
  75         [
  76           1 => [$url_id, 'Integer'],
  77         ]
  78       );
  79
  80       if (!$search->fetch()) {
  81         return CRM_Utils_System::baseURL();
  82       }
  83
  84       return $search->url;
  85     }
  86
  87     $open = new CRM_Mailing_Event_BAO_TrackableURLOpen();
  88     $open->event_queue_id = $queue_id;
  89     $open->trackable_url_id = $url_id;
  90     $open->time_stamp = date('YmdHis');
  91     $open->save();
  92
  93     return $search->url;
  94   }
  95
  96   /**
  97    * Get row count for the event selector.
  98    *
  99    * @param int $mailing_id
 100    *   ID of the mailing.
 101    * @param int $job_id
 102    *   Optional ID of a job to filter on.
 103    * @param bool $is_distinct
 104    *   Group by queue ID?.
 105    * @param int $url_id
 106    *   Optional ID of a url to filter on.
 107    *
 108    * @param string $toDate
 109    *
 110    * @return int
 111    *   Number of rows in result set
 112    */
 113   public static function getTotalCount(
 114     $mailing_id, $job_id = NULL,
 115     $is_distinct = FALSE, $url_id = NULL, $toDate = NULL
 116   ) {
 117     $dao = new CRM_Core_DAO();
 118
 119     $click = self::getTableName();
 120     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 121     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 122     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 123
 124     $distinct = NULL;
 125     if ($is_distinct) {
 126       $distinct = 'DISTINCT ';
 127     }
 128     $query = "
 129             SELECT      COUNT($distinct $click.event_queue_id) as opened
 130             FROM        $click
 131             INNER JOIN  $queue
 132                     ON  $click.event_queue_id = $queue.id
 133             INNER JOIN  $job
 134                     ON  $queue.job_id = $job.id
 135             INNER JOIN  $mailing
 136                     ON  $job.mailing_id = $mailing.id
 137                     AND $job.is_test = 0
 138             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 139
 140     if (!empty($toDate)) {
 141       $query .= " AND $click.time_stamp <= $toDate";
 142     }
 143
 144     if (!empty($job_id)) {
 145       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 146     }
 147
 148     if (!empty($url_id)) {
 149       $query .= " AND $click.trackable_url_id = " . CRM_Utils_Type::escape($url_id, 'Integer');
 150     }
 151
 152     // query was missing
 153     $dao->query($query);
 154
 155     if ($dao->fetch()) {
 156       return $dao->opened;
 157     }
 158
 159     return NULL;
 160   }
 161
 162   /**
 163    * Get tracked url count for each mailing for a given set of mailing IDs.
 164    *
 165    * @see https://issues.civicrm.org/jira/browse/CRM-12814
 166    *
 167    * @param array $mailingIDs
 168    *
 169    * @return array
 170    *   trackable url count per mailing ID
 171    */
 172   public static function getMailingTotalCount($mailingIDs) {
 173     $dao = new CRM_Core_DAO();
 174     $clickCount = [];
 175
 176     $click = self::getTableName();
 177     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 178     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 179     $mailingIDs = implode(',', $mailingIDs);
 180
 181     $query = "
 182       SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
 183       FROM $click
 184       INNER JOIN $queue
 185         ON  $click.event_queue_id = $queue.id
 186       INNER JOIN $job
 187         ON  $queue.job_id = $job.id
 188         AND $job.is_test = 0
 189       WHERE $job.mailing_id IN ({$mailingIDs})
 190       GROUP BY civicrm_mailing_job.mailing_id
 191     ";
 192
 193     $dao->query($query);
 194
 195     while ($dao->fetch()) {
 196       $clickCount[$dao->mailingID] = $dao->opened;
 197     }
 198     return $clickCount;
 199   }
 200
 201   /**
 202    * Get tracked url count for each mailing for a given set of mailing IDs.
 203    *
 204    * @param int[] $mailingIDs
 205    *   IDs of the mailing (comma separated).
 206    * @param int $contactID
 207    *   ID of the contact.
 208    *
 209    * @return array
 210    *   Count per mailing ID
 211    */
 212   public static function getMailingContactCount($mailingIDs, $contactID) {
 213     $dao = new CRM_Core_DAO();
 214     $clickCount = [];
 215
 216     $click = self::getTableName();
 217     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 218     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 219     $mailingIDs = implode(',', $mailingIDs);
 220
 221     $query = "
 222       SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
 223       FROM $click
 224       INNER JOIN $queue
 225         ON  $click.event_queue_id = $queue.id
 226         AND $queue.contact_id = $contactID
 227       INNER JOIN $job
 228         ON  $queue.job_id = $job.id
 229         AND $job.is_test = 0
 230       WHERE $job.mailing_id IN ({$mailingIDs})
 231       GROUP BY civicrm_mailing_job.mailing_id
 232     ";
 233
 234     $dao->query($query);
 235
 236     while ($dao->fetch()) {
 237       $clickCount[$dao->mailingID] = $dao->opened;
 238     }
 239
 240     return $clickCount;
 241   }
 242
 243   /**
 244    * Get rows for the event browser.
 245    *
 246    * @param int $mailing_id
 247    *   ID of the mailing.
 248    * @param int $job_id
 249    *   Optional ID of the job.
 250    * @param bool $is_distinct
 251    *   Group by queue id?.
 252    * @param int $url_id
 253    *   Optional ID of a trackable URL to filter on.
 254    * @param int $offset
 255    *   Offset.
 256    * @param int $rowCount
 257    *   Number of rows.
 258    * @param array $sort
 259    *   Sort array.
 260    * @param int $contact_id
 261    *   Optional contact ID.
 262    *
 263    * @return array
 264    *   Result set
 265    */
 266   public static function &getRows(
 267     $mailing_id, $job_id,
 268     $is_distinct, $url_id,
 269     $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
 270   ) {
 271
 272     $dao = new CRM_Core_DAO();
 273
 274     $click = self::getTableName();
 275     $url = CRM_Mailing_BAO_TrackableURL::getTableName();
 276     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 277     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 278     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 279     $contact = CRM_Contact_BAO_Contact::getTableName();
 280     $email = CRM_Core_BAO_Email::getTableName();
 281
 282     $query = "
 283             SELECT      $contact.display_name as display_name,
 284                         $contact.id as contact_id,
 285                         $email.email as email,";
 286
 287     if ($is_distinct) {
 288       $query .= "MIN($click.time_stamp) as date,";
 289     }
 290     else {
 291       $query .= "$click.time_stamp as date,";
 292     }
 293
 294     $query .= "$url.url as url
 295             FROM        $contact
 296             INNER JOIN  $queue
 297                     ON  $queue.contact_id = $contact.id
 298             INNER JOIN  $email
 299                     ON  $queue.email_id = $email.id
 300             INNER JOIN  $click
 301                     ON  $click.event_queue_id = $queue.id
 302             INNER JOIN  $url
 303                     ON  $click.trackable_url_id = $url.id
 304             INNER JOIN  $job
 305                     ON  $queue.job_id = $job.id
 306             INNER JOIN  $mailing
 307                     ON  $job.mailing_id = $mailing.id
 308                     AND $job.is_test = 0
 309             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 310
 311     if (!empty($contact_id)) {
 312       $query .= " AND $contact.id = " . CRM_Utils_Type::escape($contact_id, 'Integer');
 313     }
 314
 315     if (!empty($job_id)) {
 316       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 317     }
 318
 319     if (!empty($url_id)) {
 320       $query .= " AND $url.id = " . CRM_Utils_Type::escape($url_id, 'Integer');
 321     }
 322
 323     if ($is_distinct) {
 324       $query .= " GROUP BY $queue.id, $url.url ";
 325     }
 326
 327     $orderBy = "sort_name ASC, {$click}.time_stamp DESC";
 328     if ($sort) {
 329       if (is_string($sort)) {
 330         $sort = CRM_Utils_Type::escape($sort, 'String');
 331         $orderBy = $sort;
 332       }
 333       else {
 334         $orderBy = trim($sort->orderBy());
 335       }
 336     }
 337
 338     $query .= " ORDER BY {$orderBy} ";
 339
 340     if ($offset || $rowCount) {
 341       //Added "||$rowCount" to avoid displaying all records on first page
 342       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 343     }
 344     CRM_Core_DAO::disableFullGroupByMode();
 345     $dao->query($query);
 346     CRM_Core_DAO::reenableFullGroupByMode();
 347     $results = [];
 348
 349     while ($dao->fetch()) {
 350       $url = CRM_Utils_System::url('civicrm/contact/view',
 351         "reset=1&cid={$dao->contact_id}"
 352       );
 353       $results[] = [
 354         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 355         'email' => $dao->email,
 356         'url' => $dao->url,
 357         'date' => CRM_Utils_Date::customFormat($dao->date),
 358       ];
 359     }
 360     return $results;
 361   }
 362
 363 }