CRM/Mailing/Event/BAO/TrackableURLOpen.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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-2015
  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
  57     $search = new CRM_Mailing_BAO_TrackableURL();
  58
  59     // To find the url, we also join on the queue and job tables.  This
  60     // prevents foreign key violations.
  61     $job = CRM_Mailing_BAO_MailingJob::getTableName();
  62     $eq = CRM_Mailing_Event_BAO_Queue::getTableName();
  63     $turl = CRM_Mailing_BAO_TrackableURL::getTableName();
  64
  65     if (!$queue_id) {
  66       $search->query("SELECT $turl.url as url from $turl
  67                     WHERE $turl.id = " . CRM_Utils_Type::escape($url_id, 'Integer')
  68       );
  69       if (!$search->fetch()) {
  70         return CRM_Utils_System::baseURL();
  71       }
  72       return $search->url;
  73     }
  74
  75     $search->query("SELECT $turl.url as url from $turl
  76                     INNER JOIN $job ON $turl.mailing_id = $job.mailing_id
  77                     INNER JOIN $eq ON $job.id = $eq.job_id
  78                     WHERE $eq.id = " . CRM_Utils_Type::escape($queue_id, 'Integer') . " AND $turl.id = " . CRM_Utils_Type::escape($url_id, 'Integer')
  79     );
  80
  81     if (!$search->fetch()) {
  82       // Whoops, error, don't track it.  Return the base url.
  83
  84       return CRM_Utils_System::baseURL();
  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     $query = "
 125             SELECT      COUNT($click.id) as opened
 126             FROM        $click
 127             INNER JOIN  $queue
 128                     ON  $click.event_queue_id = $queue.id
 129             INNER JOIN  $job
 130                     ON  $queue.job_id = $job.id
 131             INNER JOIN  $mailing
 132                     ON  $job.mailing_id = $mailing.id
 133                     AND $job.is_test = 0
 134             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 135
 136     if (!empty($toDate)) {
 137       $query .= " AND $click.time_stamp <= $toDate";
 138     }
 139
 140     if (!empty($job_id)) {
 141       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 142     }
 143
 144     if (!empty($url_id)) {
 145       $query .= " AND $click.trackable_url_id = " . CRM_Utils_Type::escape($url_id, 'Integer');
 146     }
 147
 148     if ($is_distinct) {
 149       $query .= " GROUP BY $queue.id ";
 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    * 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 = array();
 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 = array();
 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 = NULL,
 268     $is_distinct = FALSE, $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                         $click.time_stamp as date,
 287                         $url.url as url
 288             FROM        $contact
 289             INNER JOIN  $queue
 290                     ON  $queue.contact_id = $contact.id
 291             INNER JOIN  $email
 292                     ON  $queue.email_id = $email.id
 293             INNER JOIN  $click
 294                     ON  $click.event_queue_id = $queue.id
 295             INNER JOIN  $url
 296                     ON  $click.trackable_url_id = $url.id
 297             INNER JOIN  $job
 298                     ON  $queue.job_id = $job.id
 299             INNER JOIN  $mailing
 300                     ON  $job.mailing_id = $mailing.id
 301                     AND $job.is_test = 0
 302             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 303
 304     if (!empty($contact_id)) {
 305       $query .= " AND $contact.id = " . CRM_Utils_Type::escape($contact_id, 'Integer');
 306     }
 307
 308     if (!empty($job_id)) {
 309       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 310     }
 311
 312     if (!empty($url_id)) {
 313       $query .= " AND $url.id = " . CRM_Utils_Type::escape($url_id, 'Integer');
 314     }
 315
 316     if ($is_distinct) {
 317       $query .= " GROUP BY $queue.id ";
 318     }
 319
 320     $orderBy = "sort_name ASC, {$click}.time_stamp DESC";
 321     if ($sort) {
 322       if (is_string($sort)) {
 323         $sort = CRM_Utils_Type::escape($sort, 'String');
 324         $orderBy = $sort;
 325       }
 326       else {
 327         $orderBy = trim($sort->orderBy());
 328       }
 329     }
 330
 331     $query .= " ORDER BY {$orderBy} ";
 332
 333     if ($offset || $rowCount) {
 334       //Added "||$rowCount" to avoid displaying all records on first page
 335       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 336     }
 337
 338     $dao->query($query);
 339
 340     $results = array();
 341
 342     while ($dao->fetch()) {
 343       $url = CRM_Utils_System::url('civicrm/contact/view',
 344         "reset=1&cid={$dao->contact_id}"
 345       );
 346       $results[] = array(
 347         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 348         'email' => $dao->email,
 349         'url' => $dao->url,
 350         'date' => CRM_Utils_Date::customFormat($dao->date),
 351       );
 352     }
 353     return $results;
 354   }
 355
 356 }