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