CRM/Mailing/Event/BAO/TrackableURLOpen.php

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