CRM/Mailing/Event/BAO/TrackableURLOpen.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.6                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2014                                |
   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-2014
  32  * $Id$
  33  *
  34  */
  35 class CRM_Mailing_Event_BAO_TrackableURLOpen extends CRM_Mailing_Event_DAO_TrackableURLOpen {
  36
  37   /**
  38    * Class constructor
  39    */
  40   public function __construct() {
  41     parent::__construct();
  42   }
  43
  44   /**
  45    * Track a click-through and return the URL to redirect.  If the numbers
  46    * don't match up, return the base url.
  47    *
  48    * @param int $queue_id
  49    *   The Queue Event ID of the clicker.
  50    * @param int $url_id
  51    *   The ID of the trackable URL.
  52    *
  53    * @return string $url      The redirection url, or base url on failure.
  54    * @static
  55    */
  56   public static function track($queue_id, $url_id) {
  57
  58     $search = new CRM_Mailing_BAO_TrackableURL();
  59
  60     /* To find the url, we also join on the queue and job tables.  This
  61          * prevents foreign key violations. */
  62
  63     $job  = CRM_Mailing_BAO_MailingJob::getTableName();
  64     $eq   = CRM_Mailing_Event_BAO_Queue::getTableName();
  65     $turl = CRM_Mailing_BAO_TrackableURL::getTableName();
  66
  67     if (!$queue_id) {
  68       $search->query("SELECT $turl.url as url from $turl
  69                     WHERE $turl.id = " . CRM_Utils_Type::escape($url_id, 'Integer')
  70       );
  71       if (!$search->fetch()) {
  72         return CRM_Utils_System::baseURL();
  73       }
  74       return $search->url;
  75     }
  76
  77     $search->query("SELECT $turl.url as url from $turl
  78                     INNER JOIN $job ON $turl.mailing_id = $job.mailing_id
  79                     INNER JOIN $eq ON $job.id = $eq.job_id
  80                     WHERE $eq.id = " . CRM_Utils_Type::escape($queue_id, 'Integer') . " AND $turl.id = " . CRM_Utils_Type::escape($url_id, 'Integer')
  81     );
  82
  83     if (!$search->fetch()) {
  84       /* Whoops, error, don't track it.  Return the base url. */
  85
  86       return CRM_Utils_System::baseURL();
  87     }
  88
  89     $open = new CRM_Mailing_Event_BAO_TrackableURLOpen();
  90     $open->event_queue_id = $queue_id;
  91     $open->trackable_url_id = $url_id;
  92     $open->time_stamp = date('YmdHis');
  93     $open->save();
  94
  95     return $search->url;
  96   }
  97
  98   /**
  99    * Get row count for the event selector
 100    *
 101    * @param int $mailing_id
 102    *   ID of the mailing.
 103    * @param int $job_id
 104    *   Optional ID of a job to filter on.
 105    * @param bool $is_distinct
 106    *   Group by queue ID?.
 107    * @param int $url_id
 108    *   Optional ID of a url to filter on.
 109    *
 110    * @return int                  Number of rows in result set
 111    * @static
 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    * CRM-12814
 164    * Get tracked url count for each mailing for a given set of mailing IDs
 165    *
 166    * @param $mailingIDs
 167    *
 168    * @return array          trackable url count per mailing ID
 169    * @static
 170    */
 171   public static function getMailingTotalCount($mailingIDs) {
 172     $dao = new CRM_Core_DAO();
 173     $clickCount = array();
 174
 175     $click = self::getTableName();
 176     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 177     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 178     $mailingIDs = implode(',', $mailingIDs);
 179
 180     $query = "
 181       SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
 182       FROM $click
 183       INNER JOIN $queue
 184         ON  $click.event_queue_id = $queue.id
 185       INNER JOIN $job
 186         ON  $queue.job_id = $job.id
 187         AND $job.is_test = 0
 188       WHERE $job.mailing_id IN ({$mailingIDs})
 189       GROUP BY civicrm_mailing_job.mailing_id
 190     ";
 191
 192     $dao->query($query);
 193
 194     while ( $dao->fetch() ) {
 195       $clickCount[$dao->mailingID] = $dao->opened;
 196     }
 197     return $clickCount;
 198   }
 199
 200   /**
 201    * Get tracked url count for each mailing for a given set of mailing IDs
 202    *
 203    * @param int $mailingIDs
 204    *   IDs of the mailing (comma separated).
 205    * @param int $contactID
 206    *   ID of the contact.
 207    *
 208    * @return array             Count per mailing ID
 209    * @static
 210    */
 211   public static function getMailingContactCount($mailingIDs, $contactID) {
 212     $dao = new CRM_Core_DAO();
 213     $clickCount = array();
 214
 215     $click = self::getTableName();
 216     $queue = CRM_Mailing_Event_BAO_Queue::getTableName();
 217     $job = CRM_Mailing_BAO_MailingJob::getTableName();
 218     $mailingIDs = implode(',', $mailingIDs);
 219
 220     $query = "
 221       SELECT $job.mailing_id as mailingID, COUNT($click.id) as opened
 222       FROM $click
 223       INNER JOIN $queue
 224         ON  $click.event_queue_id = $queue.id
 225         AND $queue.contact_id = $contactID
 226       INNER JOIN $job
 227         ON  $queue.job_id = $job.id
 228         AND $job.is_test = 0
 229       WHERE $job.mailing_id IN ({$mailingIDs})
 230       GROUP BY civicrm_mailing_job.mailing_id
 231     ";
 232
 233     $dao->query($query);
 234
 235     while ( $dao->fetch() ) {
 236       $clickCount[$dao->mailingID] = $dao->opened;
 237     }
 238
 239     return $clickCount;
 240   }
 241
 242   /**
 243    * Get rows for the event browser
 244    *
 245    * @param int $mailing_id
 246    *   ID of the mailing.
 247    * @param int $job_id
 248    *   Optional ID of the job.
 249    * @param bool $is_distinct
 250    *   Group by queue id?.
 251    * @param int $url_id
 252    *   Optional ID of a trackable URL to filter on.
 253    * @param int $offset
 254    *   Offset.
 255    * @param int $rowCount
 256    *   Number of rows.
 257    * @param array $sort
 258    *   Sort array.
 259    * @param int $contact_id
 260    *   Optional contact ID.
 261    *
 262    * @return array                Result set
 263    * @static
 264    */
 265   public static function &getRows(
 266     $mailing_id, $job_id = NULL,
 267     $is_distinct = FALSE, $url_id,
 268     $offset = NULL, $rowCount = NULL, $sort = NULL, $contact_id = NULL
 269   ) {
 270
 271     $dao = new CRM_Core_Dao();
 272
 273     $click   = self::getTableName();
 274     $url     = CRM_Mailing_BAO_TrackableURL::getTableName();
 275     $queue   = CRM_Mailing_Event_BAO_Queue::getTableName();
 276     $mailing = CRM_Mailing_BAO_Mailing::getTableName();
 277     $job     = CRM_Mailing_BAO_MailingJob::getTableName();
 278     $contact = CRM_Contact_BAO_Contact::getTableName();
 279     $email   = CRM_Core_BAO_Email::getTableName();
 280
 281     $query = "
 282             SELECT      $contact.display_name as display_name,
 283                         $contact.id as contact_id,
 284                         $email.email as email,
 285                         $click.time_stamp as date,
 286                         $url.url as url
 287             FROM        $contact
 288             INNER JOIN  $queue
 289                     ON  $queue.contact_id = $contact.id
 290             INNER JOIN  $email
 291                     ON  $queue.email_id = $email.id
 292             INNER JOIN  $click
 293                     ON  $click.event_queue_id = $queue.id
 294             INNER JOIN  $url
 295                     ON  $click.trackable_url_id = $url.id
 296             INNER JOIN  $job
 297                     ON  $queue.job_id = $job.id
 298             INNER JOIN  $mailing
 299                     ON  $job.mailing_id = $mailing.id
 300                     AND $job.is_test = 0
 301             WHERE       $mailing.id = " . CRM_Utils_Type::escape($mailing_id, 'Integer');
 302
 303     if (!empty($contact_id)) {
 304       $query .= " AND $contact.id = " . CRM_Utils_Type::escape($contact_id, 'Integer');
 305     }
 306
 307     if (!empty($job_id)) {
 308       $query .= " AND $job.id = " . CRM_Utils_Type::escape($job_id, 'Integer');
 309     }
 310
 311     if (!empty($url_id)) {
 312       $query .= " AND $url.id = " . CRM_Utils_Type::escape($url_id, 'Integer');
 313     }
 314
 315     if ($is_distinct) {
 316       $query .= " GROUP BY $queue.id ";
 317     }
 318
 319     $orderBy = "sort_name ASC, {$click}.time_stamp DESC";
 320     if ($sort) {
 321       if (is_string($sort)) {
 322         $sort = CRM_Utils_Type::escape($sort, 'String');
 323         $orderBy = $sort;
 324       }
 325       else {
 326         $orderBy = trim($sort->orderBy());
 327       }
 328     }
 329
 330     $query .= " ORDER BY {$orderBy} ";
 331
 332     if ($offset || $rowCount) {
 333       //Added "||$rowCount" to avoid displaying all records on first page
 334       $query .= ' LIMIT ' . CRM_Utils_Type::escape($offset, 'Integer') . ', ' . CRM_Utils_Type::escape($rowCount, 'Integer');
 335     }
 336
 337     $dao->query($query);
 338
 339     $results = array();
 340
 341     while ($dao->fetch()) {
 342       $url = CRM_Utils_System::url('civicrm/contact/view',
 343         "reset=1&cid={$dao->contact_id}"
 344       );
 345       $results[] = array(
 346         'name' => "<a href=\"$url\">{$dao->display_name}</a>",
 347         'email' => $dao->email,
 348         'url' => $dao->url,
 349         'date' => CRM_Utils_Date::customFormat($dao->date),
 350       );
 351     }
 352     return $results;
 353   }
 354 }